import getpass
import psycopg2
from sqlalchemy import create_engine
from psycopg2.extensions import register_adapter, AsIs
from IPython.display import Image
Even before the age of digitalization, people have been exploring different means of self-promotion in order to be able to find desired partners or friends that are outside of one’s network. As early as the 1600s, different bachelors have explored using newspapers as a way to look for potential suitors [1]. This practice has continued until the 1960s, where the first computer-assisted matching platforms were developed by Harvard students. Dubbed as Operation Match, this platform utilizes a 75 question survey for individuals who would like to find potential suitors and then utilizes an IBM 1401 computer to generate a list of potential matches for each individual in exchange for a fee. These initial efforts in utilizing technology to form connections between people has led to the evolution of the online dating space. In the early 21st century, matchmaking platforms would leverage the results of different psychological tests and then apply statistical methods such as factor analysis to identify the profiles of each user. The recommended matches generated by these methods would highly likely come from those that have similar profiles as the user. However, different researchers that take an interest in the mechanisms behind online dating have critiqued the possibility of generating inaccurate matches when using survey data in finding match recommendations due to prevalent discrepancies between the characteristics of the person as reflected on the survey results versus their actual personalities as they reveal themselves to the other party. Likewise, the responses of people on a survey may only be relevant at one particular point in time. Hence, this methodology does not capture the dynamic changes in the characteristics of individuals.
With this, players in the technology industry have explored the utilization of online dating applications which attempt to integrate information that are reflective of the user’s current characteristics - such as demographic information on social media along with user detected activity when navigating through the internet. The widespread use of social media which started in the late 2000s has led to the curation of different online dating platforms targeted towards specific kinds of demographics, including the members of the LGBTQ+ community along with other minority groups. Tinder, which was established in 2012, is a global online dating platform that aims to connect different people who have the potential to be romantic lovers or very close friends. This dating platform is often used by travelers who would like to meet locals in the country that they are traveling to. The platform tends to use collected user demographics, social media activity data, along with the user’s level of activity in Tinder to generate a compilation of profiles which may be of interest to the user. Once this list of potential matches was generated by the application, each user can either swipe right to express their interest towards meeting the potential match or swipe left if they are not interested. Tinder is the most downloaded dating platform in the entire world as of 2021, and is available in more than 190 countries and 40 languages.
Although Tinder can effectively generate recommendations to each user for potential matches, it is more likely that the relationships formed from existing online platforms do not last for a significant period of time. Given Tinder’s reputation of being a “hookup app”, there may be differences in the expectations of two different individuals meeting together for the first time in terms of what they are looking for in a relationship. Moreover, it is also likely that the individuals that meet through the app would have unmatched expectations due to differences between the personas defined by each user through the dating application in relation to their actual personalities in real life.
The team believes that using demographic information as the sole basis for making user recommendations could lead to friendships and relationships that are fragile as Tinder tends to only use information inputted by each user to create profiles. Although Tinder tends to use social media information for users that log in through OAuth, this information collected may actually be not useful given that it is easy for individuals to make alternative personas online. With this, the team believes that it is necessary for dating platforms to utilize information that reveals the characteristics of each individual user as this is indicative of their actual behaviors and personalities. Hence, our team of data engineers have made Tindify, a matchmaking application that uses song streaming information of each user (which is collected from Spotify) in order to match two individuals who have similar music tastes.
In making the application, the team considered that the application should have a relatively similar design as Tinder in terms of system performance. The application should have a low latency so that when users log in to their own profiles, they are able to immediately see their recommended matches upon putting in their user credentials. Likewise, the system must be able to allow song listening logs (which are in JSON format) to be read into the database and to be integrated with CSV file formats which contain information regarding the characteristics of each song. The system must also be made to allow for existing users to listen to new songs and for new songs to be added in the existing database.
Similar to Tinder’s existing system design, it is okay for the user profiles to be not updated in real time[3] as it may take some time before a particular user would actually be shown to different potential matches. It must be noted though that this does not mean that the application would not be able to generate recommended matches instantly, rather new information regarding each user (in particular, song listening activity) need not to be integrated immediately in real time when making user recommended matches.
Lastly, Tindify must have the capability to generate recommendations which takes into account the distance preferences of each user. Since Tindify would like to encourage people to meet together and continue their conversations outside the dating application, the platform must not match together two different people who are geographically far from each other even though they have similar music listening preferences. The recommendation engine employed by the system must first filter candidate matches on the basis of the location criteria before looking at their similarity in music tastes.
Features:
Specifications:
Processes:
The data architecture of Tindify is designed based on its features and specifications. Figure below shows the overview of the Data Architecture.
Image(filename='./fp_images/Archi.png')
Figure: Architectural Diagram of Tindify
Tindify is hosted on Amazon Web Services or AWS Cloud and its data architecture is composed of the following:
As we envision Tindify to operate at scale comparable to Tinder and to handle data of different structures, it will need a repository design to store, process and secure large amounts of structured, semi structured and unstructured data. For this purpose, we design the data architecture to include a data lake hosted in an Amazon S3 bucket that will handle streaming data from the app.
Since it is expected that the Tindify app will generate large amounts of data during streaming, it is imperative to have a data architecture that consumes data immediately as it is generated. Hence, data lake is found to be the most suitable since it does not require data processing during ingestion. This ensures smooth and uninterrupted streams of events. Aside from the stream data, which is an unstructured data in JSON file format, song features data will also be ingested in the data lake. Song features data will be either structured or semi-structured. These data will be ingested to the data lake by batch, see Figure 2 for the screen shots of the file formats.
Tindify’s data lake is composed of the following zones:
Sensitive - For this service, user data that is considered sensitive and will be stored here. At the time, synthetic data used so no files are currently stored here.
Recommender - Outputs generated from the recommender systems are stored here to be pulled by the API.
User Permissions
Data Engineers - Landing Zone, Work Zone
Data Steward - Sensitive Zone
Data Scientists - Work Zone, Gold Zone, Recommender
Business Analysts - Gold Zone
Image(filename='./fp_images/S31.png')
Image(filename='./fp_images/S32.png')
Image(filename='./fp_images/S33.png')
Image(filename='./fp_images/S34.png')
Image(filename='./fp_images/S35.png')
Image(filename='./fp_images/S36.png')
Image(filename='./fp_images/S37.png')
Image(filename='./fp_images/S38.png')
Image(filename='./fp_images/S39.png')
Image(filename='./fp_images/S310.png')
Image(filename='./fp_images/S311.png')
Image(filename='./fp_images/S312.png')
The data lake would also contain CSV files containing a list of the music characteristics of each song being listened to by every user. The CSV files contain song characteristics collected from the Spotify API, such as a song’s instrumentality, valence, danceability, etc. This information would be used to find the potential matches for a user. Seen in Figure 3 is a snippet of the CSV files stored in the S3 bucket.
Image(filename='./fp_images/files.png')
Figure: Raw Data File Formats of Files are grouped by time interval
Image(filename='./fp_images/logs.png')
Figure: Content of the files
Image(filename='./fp_images/music_feats.png')
Figure: Song Characteristics
Amazon DynamoDB is the NoSQL database used in this data architecture. Since we are dealing with streaming data, which is unstructured, NoSQL allows us to store them in huge amounts, giving more flexibility on handling them.
Schema
Image(filename='./fp_images/DDB1.png')
The Primary Key is composed of the userID attribute is used as the Partition Key while the timestamp attribute is used as the Sort Key. These Keys allow us to query the necessary information needed per batch and by user.
Image(filename='./fp_images/DDB2.png')
Image(filename='./fp_images/DDB3.png')
Image(filename='./fp_images/DDB4.png')
Image(filename='./fp_images/DDB5.png')
Image(filename='./fp_images/DDB6.png')
Image(filename='./fp_images/DDB7.png')
Image(filename='./fp_images/DDB8.png')
Amazon RDS is the Online Transaction Processing (OLTP) database of the Tindify Architecture. This stores the following relevant information:
The information stored here will be then used for data mining and business analytics, therefore, this database will be made accessible to relevant users in the organization to perform certain operations relevant to the business and can be also used for further processing.
The input data for the User Profile and User Song Streaming Activity comes from Amazon DynamoDB while the Song Features and Attributes and Song Artist Information input data comes from the data lake. They are transformed prior loading to the OLTP database.
Image(filename='./fp_images/RDS1.png')
Schema
The OLTP database is used in order to be able to integrate the different data sources used in generating matches for each user on the basis of their song preferences. The OLTP database would also be used as the main source for extracting the song feature vectors for each user looking for a match on Tindify. The OLTP database consists of five normalized tables, namely users, user locations, listens, artists, and songs. The songs table contains all of the characteristics of songs that are being listened to by each user, whereas the artists table contains the singers (could be multiple) for each song. The listens table contains the title of the song being listened to by the user and the timestamp in which the song is actually listened by the user. The users table would contain demographic information about each user on Tindify, along with the type of their subscription on Tindify. Lastly, each user tends to reside on a specific location which is represented by geospatial coordinates.
Image(filename='./fp_images/oltp_schema.png')
Amazon Redshift is the Online Analytical Processing (OLAP) database of the Tindify Data Architecture. The input data comes from the OLTP database (Amazon RDS) that is transformed prior loading.
Data analysis on users and songs are performed here which are then reported through the dashboard.
Image(filename='./fp_images/Redshift1.png')
Schema
The OLAP database above considers the process of a user listening to a particular song, such that the grain is represented by each song being listened to by a user. The fact table of the OLAP schema would contain the song that a user would listen to at a particular point in time, along with information related to each user such as the artist of the song that they are listening to and their current location. Linked to the fact table are five different dimensions - the song dimension which contains the different song features that are used in generating recommendations, the artist of the song being listened to by the user, the location of each user, some demographic information about the user, and the particular point in time in which the user listens to a given song. This OLAP database would be connected to Google Data Studio in order to generate data visualizations that provide Tindify an idea of the characteristics of the users that are looking for matches through the application.
Image(filename='./fp_images/olap_schema.png')
1. Stream Logs from S3 Data Lake to NoSQL Database (DynamoDB)
Raw user stream logs data are extracted from the data lake and are transformed to the format required . During transformation, the data is cleaned and formatted according to the format requirements of NoSQL. This ETL job will be done by batch every hour.
Schedule: Every hour
Image(filename='./fp_images/etl1.png')
Figure: User Stream Logs Data Flow to AmazonDynamoDB
2. Processed stream logs from NoSQL Database (DynamoDB) to OLTP Database (Amazon RDS)
The user stream logs data processed in Amazon DynamoDB will be transformed to suit the required OLTP schema (refer to Figure 6). This will be done by batch every hour. The normalized form of the data is needed for the Recommender System. This will be done every hour since recommendation of matches is not required to be real time. In addition, this will also be used for data analytics in OLAP, hence, does not require them to be in real time.
Schedule: Every hour
Image(filename='./fp_images/etl2.png')
Figure: Processed User Stream Logs Data Flow to Amazon RDS
3. Song Features from S3 Data Lake to to OLTP Database (Amazon RDS)
Song Features data are extracted from the data lake and are transformed to the format required by the schema (refer to Figure 6). This will be done by batch every hour. The normalized form of the data is needed for the Recommender System. This will be done every hour since recommendation of matches is not required to be real time. In addition, this will also be used for data analytics in OLAP, hence, does not require them to be in real time.
Schedule: Every hour
Image(filename='./fp_images/etl3.png')
4. Processed stream logs from OLTP Database (Amazon RDS) and preprocessed song features from S3 Data Lake to OLAP Database (Amazon Redshift)
Processed user and song information data will be extracted from the OLTP database and then transformed to suit the required schema , refer to Figure 7. This will be done by batch daily. The normalized form of the data will be used in the OLAP database to perform the data analytics as required by the business and reporting of the data analytics through the dashboard is done daily, hence real time processing will not be required.
Image(filename='./fp_images/etl4.png')
Location Matching
For matching based on location, the Haversine algorithm [4] was used. It calculates the shortest distance between two points using the following formula.
Image(filename='./fp_images/haversine.png')
The input data comes from the user location table in the RDS, which are vectorized and broadcasted to the formula for the pairwise comparison of the user’s location relative to the location of all other registered users. Individuals that are not within the distance threshold that was inputted by the user would not be included anymore on the list of potential candidates for that specific user’s matches. Their similarity of song preferences would not be calculated anymore.
Song Matching
Once a list of candidates for potential candidates have been generated, the top three users in the database with the most similar song preferences relative to the one looking for potential matches would be identified by employing an information retrieval based recommender system. The cosine similarity metric would be used in order to identify the top three matches that have the most similar song feature vectors relative to the characteristics of the songs that are being listened to by the user. For each user, the average value of the song features (e.g. valence, instrumentality, etc.) would be calculated and the resulting vector for each user would be used for similarity calculation.
The recommendation results in CSV format are pushed into the REST API Gateway. That app then pulls the data from the API to show the match results to the users.
Image(filename='./fp_images/APIGateway.png')
Tindify App will provide users his/her matches based on their song preferences and proximity with other users. The user will just choose his/her preferred distance threshold. The proof-of-concept app was created using tkinter, which simply filters the specific user information from the generated recommender matrices.
Image(filename='./fp_images/app.png')
To allow Tindify’s data scientists to have an understanding regarding the usage of the application along with the characteristics of the users that utilize the matching platform, a dashboard containing the different data visualizations would be generated from the collected user and song streaming data. After generating an OLAP database via Amazon Redshift, the resulting data warehouse was connected to Google Data Studio in order to generate summary statistics containing the most frequently listened song per day, along with the gender distribution of users in the application and the number of songs that each user tends to listen at a particular point in time. The dashboard is updated with new data by batch. (Refer to Figure 18 for the Sample of the Dashboard)
Image(filename='./fp_images/dashboard.png')
ETL operations were implemented through a single DAG called fp_dag in Apache Airflow. The DAG is comprised of 7 tasks, with the last task just a confirmation of a run of the graph. Each task is divided into its own .py file and is imported into the main fp_dag.py file for the actual run of each file. The code behind the implementation heavily relied on the use of Boto3 and Psycopg2.
Image(filename='./fp_images/AF1.png')
Image(filename='./fp_images/AF2.png')
Image(filename='./fp_images/AF3.png')
import datetime
from update_ddb import update_ddb
from update_oltp import update_oltp
from recsys_music import recsys_music
from recsys_locs import recsys_locs
from user_map import user_map
from update_olap import update_olap
from airflow import DAG
from airflow.decorators import task
from airflow.operators.python import PythonOperator
with DAG(
'fp_dag',
start_date=datetime.datetime(2022, 6, 18),
schedule_interval='@hourly'
) as dag:
update_ddb = PythonOperator(
task_id='update_ddb',
python_callable= update_ddb
)
update_oltp = PythonOperator(
task_id='update_oltp',
python_callable= update_oltp
)
recsys_music = PythonOperator(
task_id='recsys_music',
python_callable= recsys_music
)
recsys_locs = PythonOperator(
task_id='recsys_locs',
python_callable= recsys_locs
)
user_map = PythonOperator(
task_id='user_map',
python_callable= user_map
)
update_olap = PythonOperator(
task_id='update_olap',
python_callable= update_olap
)
@task
def dag_status():
print('Success')
update_ddb >> update_oltp >> [recsys_music, recsys_locs, user_map, update_olap] >> dag_status()
Task 1: update_ddb.py - The first task pulls the raw .json logs from the S3 bucket and transforms the files to be uploaded into DynamoDB then saves the data into the datalake as json files
import glob
import json
import pandas as pd
import boto3
from dynamodb_json import json_util
def update_ddb():
bucket_name = 'finalproj-s3'
_PREFIX = 'landing/logs'
s3 = boto3.resource('s3')
bucket = s3.Bucket(bucket_name)
s3_client = boto3.client('s3')
dynamodb = boto3.resource('dynamodb', region_name='us-east-1')
logs = dynamodb.Table('fp-logs')
ddb_client = boto3.client('dynamodb')
for s3_object in bucket.objects.filter(Prefix=_PREFIX).all():
if s3_object.key == _PREFIX:
continue
else:
content_object = s3.Object(bucket_name, s3_object.key)
file_content = content_object.get()['Body']
json_list = [json.loads(line) for line in file_content.iter_lines()]
json_df = pd.DataFrame(json_list)
for col in json_df.columns:
try:
json_df[col] = json_df[col].str.replace('\'','`')
except:
continue
json_dict = json_df.to_dict('records')
for row in json_dict:
try:
ddb_client.execute_statement(
Statement=f"""
INSERT INTO "fp-logs" VALUE {str(row)}
"""
)
except:
continue
ddb_output = ddb_client.execute_statement(
Statement="""
SELECT *
FROM "fp-logs"
"""
)['Items']
s3object = s3.Object(bucket_name, 'work/nosql/ddb.json')
s3object.put(Body=(bytes(json.dumps(ddb_output).encode('UTF-8'))))
Task 2: update_oltp - This taks pulls the data from the DynamoDB table and performs the ETL process into the Postgresql database then saves the data into the datalake as csv files
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from ast import literal_eval
import boto3
import json
import itertools
import getpass
from dynamodb_json import json_util
import io
import psycopg2
from sqlalchemy import create_engine
from psycopg2.extensions import register_adapter, AsIs
def update_oltp():
dynamodb = boto3.resource('dynamodb', region_name='us-east-1')
logs = dynamodb.Table('fp-logs')
ddb_client = boto3.client('dynamodb')
ddb_output = ddb_client.execute_statement(
Statement="""
SELECT *
FROM "fp-logs"
"""
)['Items']
ddb_df = pd.DataFrame(json_util.loads(ddb_output))
users_df = ddb_df[['userID', 'userFullName', 'gender', 'level']].copy()
users_df = users_df.drop_duplicates(subset='userID', keep='last')
users_df = users_df.sort_values(by='userID').reset_index(drop=True)
locs_df = ddb_df[['userID', 'longitude', 'latitude']].copy()
locs_df = locs_df.drop_duplicates(subset='userID', keep='last')
locs_df = locs_df.sort_values(by='userID').reset_index(drop=True)
songs = pd.read_csv('s3://finalproj-s3/landing/music/songs.csv')
songs['artists'] = songs['artists'].apply(literal_eval)
songs = songs.explode('artists')
artists_df = songs[['artists']].dropna().drop_duplicates().reset_index(drop=True).reset_index().copy()
artists_df = artists_df.rename(columns={'index':'artist_id'})
songs_df = songs[['id', 'name', 'release_date', 'year',
'duration_ms', 'acousticness', 'danceability',
'energy', 'explicit', 'instrumentalness', 'key',
'liveness', 'loudness', 'mode', 'popularity', 'speechiness',
'tempo', 'valence']].drop_duplicates()
songs_df = songs_df.rename(columns={'name':'song_title'})
songs_artists_df = songs[['name', 'artists']].reset_index(drop=True)
songs_artists_df = songs_artists_df.rename(columns={'name':'song_title'})
listens_df = ddb_df[['timestamp', 'userID', 'song_title']].copy()
listens_df = listens_df.sort_values(by='timestamp').reset_index(drop=True)
with open('creds.txt') as f:
creds = f.read()
engine = psycopg2.connect(
database = 'love_database',
host = "finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com",
user = "love_keeper",
password = creds
)
cur = engine.cursor()
engine.set_session(autocommit=True)
users_insert = ("""
INSERT INTO users (
userID,
userFullName,
gender,
level
)
VALUES (%s, %s, %s, %s);
""")
for i, row in users_df.iterrows():
try:
cur.execute(users_insert, row)
engine.commit()
except:
continue
artists_insert = ("""
INSERT INTO artists (
artist_id,
artists
)
VALUES (%s, %s);
""")
for i, row in artists_df.iterrows():
try:
cur.execute(artists_insert, row)
engine.commit()
except:
continue
songs_insert = ("""
INSERT INTO songs (song_id, song_title, release_date, year, duration_ms,
acousticness, danceability, energy, explicit, instrumentalness, key,
liveness, loudness, mode, popularity, speechiness, tempo, valence)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s);
""")
for i, row in songs_df.iterrows():
try:
cur.execute(songs_insert, tuple(row))
engine.commit()
except:
continue
listens_insert = ("""
INSERT INTO listens (
timestamp,
userID,
song_title
)
VALUES (%s, %s, %s);
""")
for i, row in listens_df.iterrows():
try:
cur.execute(listens_insert, row)
engine.commit()
except:
continue
locations_insert = ("""
INSERT INTO locations (
userID,
longitude,
latitude
)
VALUES (%s, %s, %s);
""")
for i, row in locs_df.iterrows():
try:
cur.execute(locations_insert, row)
engine.commit()
except:
continue
song_artist_insert = ("""
INSERT INTO songs_artists (
song_title,
artists
)
VALUES (%s, %s);
""")
for i, row in songs_artists_df.iterrows():
try:
cur.execute(song_artist_insert, row)
engine.commit()
except:
continue
bucket_name = 'finalproj-s3'
path = 'finalproj-s3/work/oltp'
s3 = boto3.resource('s3')
bucket = s3.Bucket(bucket_name)
s3_client = boto3.client('s3')
tables = ['artists', 'listens', 'locations', 'songs', 'users']
for table in tables:
try:
query = f"""COPY {table} TO STDIN WITH (
FORMAT csv,
DELIMITER ',',
QUOTE '"',
HEADER TRUE
)"""
file = io.StringIO()
cur.copy_expert(query, file)
s3_client.put_object(Bucket=bucket_name, Key=f'work/oltp/{table}.csv', Body=file.getvalue())
except:
continue
Task 3: update_olap - This taks pulls the data from the RDS tables and performs the ETL process into the Redshift database while saving the data into the datalake as csv files
import pandas as pd
import psycopg2
from datetime import datetime
def update_olap():
with open('creds.txt') as f:
creds = f.read()
engine = psycopg2.connect(
database = 'love_database',
host="finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com",
user="love_keeper",
password=creds
)
cur = engine.cursor()
engine.set_session(autocommit=True)
query = """
SELECT *
FROM users
"""
users_df = pd.read_sql(query, engine)
users_df = users_df.reset_index().rename(columns={'index': 'id'})
users_df['id'] = users_df['id'].astype(int)
query = """
SELECT *
FROM artists
"""
artists_df = pd.read_sql(query, engine)
query = """
SELECT *
FROM songs
"""
songs_df = pd.read_sql(query, engine)
songs_df['month'] = songs_df['release_date'].apply(lambda x: x.month)
songs_df['day'] = songs_df['release_date'].apply(lambda x: x.day)
songs_df = songs_df[['song_id', 'song_title', 'year', 'month', 'day',
'duration_ms', 'acousticness', 'danceability', 'energy',
'explicit', 'instrumentalness', 'key', 'liveness', 'loudness',
'mode', 'popularity', 'speechiness', 'tempo', 'valence']]
query = """
SELECT *
FROM listens
"""
listens_df = pd.read_sql(query, engine)
listens_df['userid'] = listens_df['userid'].astype(int)
listens_df['timestamp'] = listens_df['timestamp'].astype(int).apply(datetime.fromtimestamp)
listens_df['year'] = listens_df['timestamp'].apply(lambda x: x.year)
listens_df['month'] = listens_df['timestamp'].apply(lambda x: x.month)
listens_df['day'] = listens_df['timestamp'].apply(lambda x: x.day)
listens_df['hour'] = listens_df['timestamp'].apply(lambda x: x.hour)
listens_df['minute'] = listens_df['timestamp'].apply(lambda x: x.minute)
listens_df['second'] = listens_df['timestamp'].apply(lambda x: x.second)
query = """
SELECT *
FROM locations
"""
loc_df = pd.read_sql(query, engine)
loc_df['userid'] = loc_df['userid'].astype(float).astype(int)
with open('creds2.txt') as f:
creds2 = f.read()
engine_2 = psycopg2.connect(
database="songsdb",
host="new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com",
user="songmanager",
password=creds2,
port="5439"
)
cur_2 = engine_2.cursor()
engine_2.set_session(autocommit=True)
users_insert = ("""
INSERT INTO dim_users (id,
user_id,
full_name,
gender,
level
)
VALUES (%s, %s, %s, %s, %s);
""")
for i, row in users_df.iterrows():
try:
cur_2.execute(users_insert, tuple(row.values))
engine_2.commit()
except:
continue
locations_insert = ("""
INSERT INTO dim_locations (
user_id,
longitude,
latitude
)
VALUES (%s, %s, %s);
""")
for i, row in loc_df.iterrows():
try:
cur_2.execute(locations_insert, tuple(row.values))
engine_2.commit()
except:
continue
artists_insert = ("""
INSERT INTO dim_artists (
artist_id,
artist
)
VALUES (%s, %s);
""")
for i, row in artists_df.iterrows():
try:
cur_2.execute(artists_insert, tuple(row.values))
engine_2.commit()
except:
continue
songs_insert = ("""
INSERT INTO dim_songs (
song_id, song_title, release_date,
year, duration_ms, acousticness,
danceability,
energy,
explicit,
instrumentalness,
key,
liveness,
loudness,
mode,
popularity,
speechiness,
tempo,
valence
)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s);
""")
for i, row in songs_df.iterrows():
try:
cur_2.execute(songs_insert, tuple(row.values))
engine_2.commit()
except:
continue
listens_insert = ("""
INSERT INTO fact_listens (
user_id,
song_id,
song_title,
timestamp
) VALUES (%s, %s, %s, %s);
""")
listens_merged_df = (pd.merge(listens_df, songs_df[['song_id', 'song_title']],
left_on='song_title',
right_on='song_title'))
listens_merged_df = listens_merged_df[['userid', 'song_id', 'song_title', 'year', 'month', 'day',
'hour', 'minute', 'second']]
listens_merged_df['date_id'] = list(range(1, listens_merged_df.shape[0]+1))
listens_merged_df_1 = listens_merged_df[['userid', 'song_id', 'song_title', 'date_id']]
listens_merged_df_2 = listens_merged_df[['date_id', 'year', 'month', 'day',
'hour', 'minute', 'second']]
dates_insert = ("""
INSERT INTO dim_dates (
date_id,
year,
month,
day,
hour,
minute,
second
) VALUES (%s, %s, %s, %s, %s, %s, %s)
""")
for i, row in listens_merged_df_2.astype(float).iterrows():
try:
cur_2.execute(dates_insert, tuple(row.values))
engine_2.commit()
except:
continue
listens_insert = ("""
INSERT INTO fact_listens (
user_id,
song_id,
song_title,
date_id
) VALUES (%s, %s, %s, %s);
""")
for i, row in listens_merged_df_1.iterrows():
try:
cur_2.execute(listens_insert, tuple(row.values))
engine_2.commit()
except:
continue
dim_users = pd.read_sql('SELECT * FROM dim_users', engine_2)
dim_locations = pd.read_sql('SELECT * FROM dim_locations', engine_2)
dim_artists = pd.read_sql('SELECT * FROM dim_artists', engine_2)
dim_songs = pd.read_sql('SELECT * FROM dim_songs', engine_2)
dim_dates = pd.read_sql('SELECT * FROM dim_dates', engine_2)
fact_listens = pd.read_sql('SELECT * FROM fact_listens', engine_2)
dim_users.to_csv('s3://finalproj-s3/work/olap/dim_users.csv')
dim_locations.to_csv('s3://finalproj-s3/work/olap/dim_locations.csv')
dim_artists.to_csv('s3://finalproj-s3/work/olap/dim_artists.csv')
dim_songs.to_csv('s3://finalproj-s3/work/olap/dim_songs.csv')
dim_dates.to_csv('s3://finalproj-s3/work/olap/dim_dates.csv')
fact_listens.to_csv('s3://finalproj-s3/work/olap/fact_listens.csv')
Task 4: recsys_locs - Pulls data from the OLTP database to generate the pairwise distances for each user. Tthe recommendation matrix is then saved as a csv file in the data lake.
import psycopg2
import numpy as np
import pandas as pd
from scipy.spatial import distance
def recsys_locs():
with open('creds.txt') as f:
creds = f.read()
engine = psycopg2.connect(
database = 'love_database',
host = "finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com",
user = "love_keeper",
password = creds
)
cur = engine.cursor()
engine.set_session(autocommit=True)
query = """
SELECT *
FROM locations
"""
loc_df = pd.read_sql(query, engine)
loc_df = loc_df.drop_duplicates('userid', keep='last')
lats = np.array(np.deg2rad(loc_df['latitude'].astype(float)))
lons = np.array(np.deg2rad(loc_df['longitude'].astype(float)))
diff_lat = lats[:,None] - lats
diff_lon = lons[:,None] - lons
d = np.sin(diff_lat/2)**2 + np.cos(lats[:,None])*np.cos(lats) * np.sin(diff_lon/2)**2
d = 2 * 6371 * np.arcsin(np.sqrt(d))
user_list = [uid for uid in loc_df['userid']]
dist_df = pd.DataFrame(d)
dist_df.index = user_list
dist_df.columns = user_list
dist_df.to_csv('s3://finalproj-s3/recommender/loc_match.csv', index_label='userID')
Task 5: recsys_locs - Pulls data from the OLTP database to generate the cosine similarity for the listens from each user. The recommendation matrix is then saved as a csv file in the data lake.
import psycopg2
import numpy as np
import pandas as pd
from scipy.spatial import distance
def recsys_music():
def generate_similarity_matrix(df_users):
df_sim = pd.DataFrame()
objects = df_users.iloc[:, 1:]
for user_to_rec in range(len(objects)):
query = df_users[df_users.index == user_to_rec].iloc[:, 1:]
result_dist = distance.cdist(query, objects, metric='cosine')[0]
df_sim[user_to_rec] = result_dist
return df_sim
with open('creds.txt') as f:
creds = f.read()
engine = psycopg2.connect(
database = 'love_database',
host = "finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com",
user = "love_keeper",
password = creds
)
cur = engine.cursor()
engine.set_session(autocommit=True)
query = """
SELECT *
FROM songs
"""
songs_df = pd.read_sql(query, engine)
query = """
SELECT *
FROM listens
"""
listens_df = pd.read_sql(query, engine)
query = """
SELECT *
FROM users
"""
users_df = pd.read_sql(query, engine)
songs = songs_df.drop(['song_id', 'release_date'],axis=1)
listens = listens_df[['userid', 'song_title']].drop_duplicates()
match_df = pd.merge(listens, songs, left_on='song_title', right_on='song_title').drop(['song_title'],axis=1)
song_matches = generate_similarity_matrix(match_df)
user_list = [u for u in match_df['userid']]
song_matches.index=user_list
song_matches.columns=user_list
song_matches.iloc[:-300,:-300].to_csv('s3://finalproj-s3/recommender/song_match.csv', index_label='userID')
Task 6: user_map - Pulls data from the OLTP database to create a mappings for userIDs and usernames
import psycopg2
import numpy as np
import pandas as pd
def user_map():
with open('creds.txt') as f:
creds = f.read()
engine = psycopg2.connect(
database = 'love_database',
host = "finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com",
user = "love_keeper",
password = creds
)
cur = engine.cursor()
engine.set_session(autocommit=True)
query = """
SELECT *
FROM users
"""
users_df = pd.read_sql(query, engine)
users = users_df[['userid', 'userfullname']].copy()
users.to_csv('s3://finalproj-s3/recommender/user_map.csv', index=False)
Task 7: dag_status - simply prints "Success" upon completion of the DAG
In the Tindify project, we are able to create an application that aims to match two individuals together on the basis of their proximity in location along with their similarity in the songs that they listen to. In generating the recommendations, we extracted the user information along with their respective locations via the OLTP database in order to filter out potential matches that go beyond the distance threshold defined by the user looking for matches. Those users that meet the distance criteria are then checked for compatibility by comparing their music preferences using the information-retrieval based recommender system. An OLAP database was also made for Tindify’s data scientists to monitor emerging trends in song preferences and also gain information about the characteristics of the users looking for potential matches through the application. Given that the user streaming data are in JSON log files and the song features are stored in a CSV format, a data lake was used in order to store the data.
To further improve the platform, we recommend the integration of different kinds of data that reveal user preferences instead of using information that the user just supplies or answers in a questionnaire (which has the tendency to be inaccurate, hence lowering the quality of the relationships formed through the application). Some examples of useful data which could help in matchmaking would include Netflix streaming data, Amazon e-commerce data, etc. As the number of users of the application increases, we recommend the use of Apache Kafka in order to be able to process huge streams of data from multiple users. Lastly, the platform could also utilize AWS Sagemaker in order to create deployable machine learning models in making model-based recommender systems.
%load_ext sql
with open('postgres_creds.txt') as f:
conn_string = f.read()
%sql $conn_string
pw = getpass.getpass()
%sql CREATE USER love_keeper WITH PASSWORD $pw;
%sql GRANT love_keeper TO postgres;
%sql CREATE DATABASE love_database OWNER love_keeper
pw = getpass.getpass()
%sql postgresql://love_keeper:$pw@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database
%%sql
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS songs CASCADE;
DROP TABLE IF EXISTS artists CASCADE;
DROP TABLE IF EXISTS listens CASCADE;
DROP TABLE IF EXISTS locations CASCADE;
DROP TABLE IF EXISTS songs_artists CASCADE;
DROP TABLE IF EXISTS users;
CREATE TABLE IF NOT EXISTS users (
userID INTEGER PRIMARY KEY,
userFullName VARCHAR,
gender VARCHAR,
level VARCHAR
);
DROP TABLE IF EXISTS songs CASCADE;
CREATE TABLE IF NOT EXISTS songs (
song_id VARCHAR ,
song_title VARCHAR PRIMARY KEY,
release_date TIMESTAMP,
year INTEGER,
duration_ms INTEGER,
acousticness FLOAT,
danceability FLOAT,
energy FLOAT,
explicit INTEGER,
instrumentalness FLOAT,
key INTEGER,
liveness FLOAT,
loudness FLOAT,
mode INTEGER,
popularity INTEGER,
speechiness FLOAT,
tempo FLOAT,
valence FLOAT
);
DROP TABLE IF EXISTS listens;
CREATE TABLE IF NOT EXISTS listens (
timestamp VARCHAR,
userID INTEGER REFERENCES users,
song_title VARCHAR REFERENCES songs
);
DROP TABLE IF EXISTS locations CASCADE;
CREATE TABLE IF NOT EXISTS locations (
userID INTEGER REFERENCES users,
longitude VARCHAR,
latitude VARCHAR
);
DROP TABLE IF EXISTS artists CASCADE;
CREATE TABLE IF NOT EXISTS artists (
artist_id VARCHAR,
artists VARCHAR PRIMARY KEY
);
DROP TABLE IF EXISTS songs_artists;
CREATE TABLE IF NOT EXISTS songs_artists (
artists VARCHAR REFERENCES artists,
song_title VARCHAR REFERENCES songs
);
SET NOCOUNT ON;
pw = getpass.getpass()
%sql postgresql://awsuser:$pw@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/dev
pw = getpass.getpass()
%sql CREATE USER songmanager WITH PASSWORD $pw
%sql GRANT songmanager TO awsuser;
%sql CREATE DATABASE songsdb OWNER songmanager
pw = getpass.getpass()
%sql postgresql://songmanager:$pw@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb
%%sql
DROP TABLE IF EXISTS dim_users;
CREATE TABLE IF NOT EXISTS dim_users (
id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
full_name TEXT NOT NULL,
gender TEXT NOT NULL,
level TEXT NOT NULL
);
DROP TABLE IF EXISTS dim_locations;
CREATE TABLE IF NOT EXISTS dim_locations (
user_id INTEGER NOT NULL,
longitude FLOAT,
latitude FLOAT
);
DROP TABLE IF EXISTS dim_artists;
CREATE TABLE IF NOT EXISTS dim_artists (
artist_id INTEGER NOT NULL,
artist TEXT NOT NULL
);
DROP TABLE IF EXISTS dim_songs;
CREATE TABLE IF NOT EXISTS dim_songs (
song_id TEXT NOT NULL,
title TEXT NOT NULL,
year INTEGER NOT NULL,
month INTEGER NOT NULL,
day INTEGER NOT NULL,
duration_ms INTEGER NOT NULL,
acousticness FLOAT NOT NULL,
danceability FLOAT NOT NULL,
energy FLOAT NOT NULL,
explicit INTEGER,
instrumentalness FLOAT NOT NULL,
key INTEGER NOT NULL,
liveness FLOAT NOT NULL,
loudness FLOAT NOT NULL,
mode INTEGER NOT NULL,
popularity INTEGER NOT NULL,
speechiness FLOAT NOT NULL,
tempo FLOAT NOT NULL,
valence FLOAT NOT NULL
);
DROP TABLE IF EXISTS dim_dates;
CREATE TABLE IF NOT EXISTS dim_dates (
date_id INTEGER NOT NULL,
year INTEGER NOT NULL,
month INTEGER NOT NULL,
day INTEGER NOT NULL,
hour INTEGER NOT NULL,
minute INTEGER NOT NULL,
second INTEGER NOT NULL
);
DROP TABLE IF EXISTS fact_listens;
CREATE TABLE IF NOT EXISTS fact_listens (
user_id INTEGER NOT NULL,
song_id VARCHAR NOT NULL,
title TEXT NOT NULL,
date_id INTEGER NOT NULL
);
SET NOCOUNT ON;
pw = getpass.getpass()
%sql postgresql://love_keeper:$pw@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database
'Connected: love_keeper@love_database'
%sql \d
* postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database 6 rows affected.
| Schema | Name | Type | Owner |
|---|---|---|---|
| public | artists | table | love_keeper |
| public | listens | table | love_keeper |
| public | locations | table | love_keeper |
| public | songs | table | love_keeper |
| public | songs_artists | table | love_keeper |
| public | users | table | love_keeper |
%sql \dt
* postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database 6 rows affected.
| Schema | Name | Type | Owner |
|---|---|---|---|
| public | artists | table | love_keeper |
| public | listens | table | love_keeper |
| public | locations | table | love_keeper |
| public | songs | table | love_keeper |
| public | songs_artists | table | love_keeper |
| public | users | table | love_keeper |
%sql SELECT COUNT (*) FROM artists;
* postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database 1 rows affected.
| count |
|---|
| 1157 |
%sql SELECT COUNT (*) FROM listens;
* postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database 1 rows affected.
| count |
|---|
| 4224 |
%sql SELECT COUNT (*) FROM locations;
* postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database 1 rows affected.
| count |
|---|
| 396 |
%sql SELECT COUNT (*) FROM songs;
* postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database 1 rows affected.
| count |
|---|
| 1926 |
%sql SELECT COUNT (*) FROM songs_artists;
* postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database 1 rows affected.
| count |
|---|
| 17058 |
%sql SELECT COUNT (*) FROM users;
* postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database 1 rows affected.
| count |
|---|
| 66 |
%sql SELECT * FROM artists LIMIT 100;
* postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 100 rows affected.
| artist_id | artists |
|---|---|
| 0 | David Guetta |
| 1 | Bebe Rexha |
| 2 | J Balvin |
| 3 | 6ix9ine |
| 4 | Nicki Minaj |
| 5 | Kanye West |
| 6 | Morgan Wallen |
| 7 | Ariana Grande |
| 8 | Steve Aoki |
| 9 | BTS |
| 10 | Forrest. |
| 11 | Biskwiq |
| 12 | Plies |
| 13 | Kevin Gates |
| 14 | Shinedown |
| 15 | Lil Baby |
| 16 | Gunna |
| 17 | Lil Durk |
| 18 | NAV |
| 19 | Seven Lions |
| 20 | SLANDER |
| 21 | Dabin |
| 22 | Dylan Matthew |
| 23 | Nipsey Hussle |
| 24 | Megan Thee Stallion |
| 25 | 6LACK |
| 26 | Yeek |
| 27 | Justin Timberlake |
| 28 | Chris Stapleton |
| 29 | Night Lovell |
| 30 | $uicideBoy$ |
| 31 | YoungBoy Never Broke Again |
| 32 | DaniLeigh |
| 33 | Jakob Ogawa |
| 34 | KAROL G |
| 35 | Anuel AA |
| 36 | Banda Los Sebastianes |
| 37 | Shoreline Mafia |
| 38 | Morgan Evans |
| 39 | Kelsea Ballerini |
| 40 | Wallows |
| 41 | T3R Elemento |
| 42 | Paris Shadows |
| 43 | Trippie Redd |
| 44 | N.E.R.D |
| 45 | Rihanna |
| 46 | Drake |
| 47 | A$AP Rocky |
| 48 | Moby |
| 49 | TWICE |
| 50 | Tinashe |
| 51 | XXXTENTACION |
| 52 | Goody Grace |
| 53 | gnash |
| 54 | Mitchell Tenpenny |
| 55 | Playboi Carti |
| 56 | Young Thug |
| 57 | Tyga |
| 58 | Lil Skies |
| 59 | Ryce |
| 60 | Luke Combs |
| 61 | JID |
| 62 | Clean Bandit |
| 63 | Zara Larsson |
| 64 | Abhi The Nomad |
| 65 | Harrison Sands |
| 66 | Copper King |
| 67 | Choker |
| 68 | Metro Boomin |
| 69 | Travis Scott |
| 70 | Kodak Black |
| 71 | 21 Savage |
| 72 | SHAUN |
| 73 | Conor Maynard |
| 74 | Sam Feldt |
| 75 | Sure Sure |
| 76 | Billie Eilish |
| 77 | TroyBoi |
| 78 | Phil Wickham |
| 79 | JPEGMAFIA |
| 80 | Aminé |
| 81 | Future |
| 82 | Juice WRLD |
| 83 | AViVA |
| 84 | 物語シリーズ |
| 85 | Marc Anthony |
| 86 | Will Smith |
| 87 | Bad Bunny |
| 88 | 5 Seconds of Summer |
| 89 | Alex Rose |
| 90 | Casper Magico |
| 91 | Nengo Flow |
| 92 | Bryant Myers |
| 93 | Noriel |
| 94 | Juhn Allstar |
| 95 | Miky Woodz |
| 96 | Jhay Cortez |
| 97 | Myke Towers |
| 98 | Louis The Child |
| 99 | NoMBe |
%sql SELECT * FROM listens LIMIT 100;
* postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 100 rows affected.
| timestamp | userid | song_title |
|---|---|---|
| 1541105830 | 26 | Stir Fry |
| 1541106106 | 23 | Sin (feat. Jaden Smith) |
| 1541106132 | 23 | Rock Salt and Nails (Live) |
| 1541105830 | 26 | Stir Fry |
| 1541106496 | 23 | La Forma en Que Me Miras |
| 1541106106 | 23 | Sin (feat. Jaden Smith) |
| 1541107053 | 47 | History |
| 1541106132 | 23 | Rock Salt and Nails (Live) |
| 1541107734 | 23 | Mockingbird |
| 1541108520 | 26 | Missin You Crazy |
| 1541109015 | 47 | Born To Be Yours |
| 1541109125 | 47 | REEL IT IN |
| 1541109325 | 47 | Promises (with Sam Smith) |
| 1541110994 | 47 | Victory Lap (feat. Stacy Barthe) |
| 1541160187 | 35 | WANTED U |
| 1541105830 | 26 | Stir Fry |
| 1541160570 | 45 | Promises (with Sam Smith) |
| 1541160592 | 42 | Hey Look Ma, I Made It |
| 1541160692 | 60 | Victory Lap (feat. Stacy Barthe) |
| 1541160714 | 57 | This Is It |
| 1541106496 | 23 | La Forma en Que Me Miras |
| 1541160936 | 57 | Level Up |
| 1541161024 | 57 | Born To Be Yours |
| 1541161144 | 60 | Level Up |
| 1541161175 | 57 | GTG |
| 1541161381 | 57 | Moon River |
| 1541161550 | 57 | REMEDY |
| 1541106106 | 23 | Sin (feat. Jaden Smith) |
| 1541162141 | 2 | Missin You Crazy |
| 1541162370 | 2 | This Is It |
| 1541162691 | 2 | Elastic |
| 1541162912 | 2 | 1999 |
| 1541163225 | 60 | Teenager in Love |
| 1541107053 | 47 | History |
| 1541163717 | 57 | History |
| 1541163997 | 35 | Born To Be Yours |
| 1541106132 | 23 | Rock Salt and Nails (Live) |
| 1541165583 | 42 | Missin You Crazy |
| 1541107734 | 23 | Mockingbird |
| 1541108520 | 26 | Missin You Crazy |
| 1541166273 | 1 | Born To Be Yours |
| 1541166621 | 60 | Diamond Teeth Samurai |
| 1541166847 | 37 | Butter Pecan - Bonus Track |
| 1541109015 | 47 | Born To Be Yours |
| 1541168424 | 37 | Level Up |
| 1541168577 | 45 | all the kids are depressed |
| 1541109125 | 47 | REEL IT IN |
| 1541109325 | 47 | Promises (with Sam Smith) |
| 1541170090 | 45 | RIP (feat. G-Eazy & Drew Love) |
| 1541110994 | 47 | Victory Lap (feat. Stacy Barthe) |
| 1541160187 | 35 | WANTED U |
| 1541172424 | 57 | Nice Guy (with Jessie Reyez) |
| 1541172593 | 32 | Beyond |
| 1541172804 | 1 | Teenager in Love |
| 1541173059 | 61 | The Reason |
| 1541173221 | 28 | Cut My Lip |
| 1541173396 | 32 | History |
| 1541105830 | 26 | Stir Fry |
| 1541174308 | 27 | Rock Salt and Nails (Live) |
| 1541174309 | 27 | Beautiful |
| 1541174356 | 28 | Ugotme |
| 1541174633 | 27 | Promises (with Sam Smith) |
| 1541174803 | 27 | Haunt U |
| 1541174900 | 28 | The Flute Song |
| 1541175054 | 61 | Hunnybee |
| 1541175153 | 14 | Silly Girl |
| 1541175433 | 29 | Forever Ever |
| 1541175530 | 29 | Had Me By Halftime |
| 1541175733 | 29 | Missin You Crazy |
| 1541175894 | 29 | Ugotme |
| 1541176076 | 14 | History |
| 1541176254 | 14 | Forgive You |
| 1541176327 | 14 | Airplane Mode |
| 1541176371 | 29 | Hey Look Ma, I Made It |
| 1541176739 | 32 | Rock Salt and Nails (Live) |
| 1541160570 | 45 | Promises (with Sam Smith) |
| 1541160592 | 42 | Hey Look Ma, I Made It |
| 1541177497 | 25 | The Other |
| 1541177709 | 25 | Singularity |
| 1541178038 | 25 | Talk Up (feat. Jay-Z) |
| 1541178048 | 25 | Forgive You |
| 1541179117 | 61 | Desperate Man |
| 1541160692 | 60 | Victory Lap (feat. Stacy Barthe) |
| 1541207123 | 63 | This Is It |
| 1541207150 | 34 | Missin You Crazy |
| 1541160714 | 57 | This Is It |
| 1541106496 | 23 | La Forma en Que Me Miras |
| 1541160936 | 57 | Level Up |
| 1541254670 | 63 | Someday |
| 1541161024 | 57 | Born To Be Yours |
| 1541259368 | 59 | Hold My Girl |
| 1541260356 | 54 | Victory Lap (feat. Stacy Barthe) |
| 1541260488 | 54 | The Flute Song |
| 1541161144 | 60 | Level Up |
| 1541161175 | 57 | GTG |
| 1541261259 | 42 | Shoulda Named It After Me |
| 1541261432 | 42 | Someday |
| 1541261690 | 31 | REMEDY |
| 1541161381 | 57 | Moon River |
| 1541262293 | 31 | Mockingbird |
%sql SELECT * FROM locations LIMIT 100;
* postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 100 rows affected.
| userid | longitude | latitude |
|---|---|---|
| 1 | 121.0577507 | 14.24777986 |
| 2 | 121.0244985 | 14.39202707 |
| 3 | 121.0513364 | 14.66512714 |
| 4 | 121.0323808 | 14.6287284 |
| 5 | 121.0546188 | 14.65372321 |
| 6 | 123.8736614 | 10.32727586 |
| 7 | 121.0598459 | 14.24851989 |
| 8 | 121.0553765 | 14.2407114 |
| 9 | 123.8694782 | 10.30912542 |
| 10 | 121.0327252 | 14.41835138 |
| 11 | 121.0377921 | 14.2298179 |
| 12 | 121.0364086 | 14.65605779 |
| 13 | 121.0450169 | 14.23488394 |
| 14 | 123.8524017 | 10.33690971 |
| 15 | 121.0709862 | 14.24565958 |
| 16 | 123.8014537 | 10.34546434 |
| 17 | 121.0614733 | 14.25084438 |
| 18 | 121.0350828 | 14.64738321 |
| 19 | 123.8582127 | 10.31223214 |
| 20 | 121.0308051 | 14.65929261 |
| 21 | 121.0125751 | 14.42874196 |
| 22 | 121.0265459 | 14.39094848 |
| 23 | 121.0272516 | 14.66177014 |
| 24 | 121.0639547 | 14.2394838 |
| 25 | 123.7952983 | 10.31724588 |
| 26 | 121.0330756 | 14.64205712 |
| 27 | 121.0732049 | 14.25466135 |
| 28 | 123.8251368 | 10.34494141 |
| 29 | 123.8054298 | 10.31592916 |
| 30 | 120.9887098 | 14.44612893 |
| 31 | 121.0488945 | 14.23072357 |
| 32 | 121.0717828 | 14.25677277 |
| 33 | 123.8028315 | 10.32714818 |
| 34 | 121.0566622 | 14.6427324 |
| 35 | 121.063723 | 14.24150137 |
| 36 | 123.8747406 | 10.3220519 |
| 37 | 121.0253021 | 14.38489132 |
| 38 | 123.8645445 | 10.31884932 |
| 39 | 121.0586424 | 14.23406803 |
| 40 | 121.0385185 | 14.39207613 |
| 41 | 121.0554547 | 14.24165886 |
| 42 | 121.029903 | 14.4049765 |
| 43 | 123.8328213 | 10.31070784 |
| 44 | 121.0056655 | 14.44359233 |
| 45 | 121.0756029 | 14.25468692 |
| 46 | 121.0593643 | 14.65222826 |
| 47 | 121.0398971 | 14.6628413 |
| 48 | 123.8737461 | 10.33265349 |
| 49 | 121.0474131 | 14.66397988 |
| 50 | 121.0345234 | 14.44554349 |
| 51 | 121.0414076 | 14.64044368 |
| 52 | 123.8708662 | 10.3218514 |
| 53 | 121.0464644 | 14.65888188 |
| 54 | 121.0288163 | 14.43105088 |
| 55 | 121.0158293 | 14.6647035 |
| 56 | 121.059258 | 14.6319537 |
| 57 | 121.0503601 | 14.64953264 |
| 58 | 121.0298833 | 14.40808912 |
| 59 | 121.0627138 | 14.23774099 |
| 60 | 121.017674 | 14.43148611 |
| 61 | 123.83282 | 10.30009076 |
| 62 | 121.0201485 | 14.3833865 |
| 63 | 121.0398209 | 14.43897897 |
| 64 | 121.0035655 | 14.44066566 |
| 65 | 121.0397764 | 14.44222001 |
| 66 | 121.0318088 | 14.62332026 |
| 1 | 121.0577507 | 14.24777986 |
| 2 | 121.0244985 | 14.39202707 |
| 3 | 121.0513364 | 14.66512714 |
| 4 | 121.0323808 | 14.6287284 |
| 5 | 121.0546188 | 14.65372321 |
| 6 | 123.8736614 | 10.32727586 |
| 7 | 121.0598459 | 14.24851989 |
| 8 | 121.0553765 | 14.2407114 |
| 9 | 123.8694782 | 10.30912542 |
| 10 | 121.0327252 | 14.41835138 |
| 11 | 121.0377921 | 14.2298179 |
| 12 | 121.0364086 | 14.65605779 |
| 13 | 121.0450169 | 14.23488394 |
| 14 | 123.8524017 | 10.33690971 |
| 15 | 121.0709862 | 14.24565958 |
| 16 | 123.8014537 | 10.34546434 |
| 17 | 121.0614733 | 14.25084438 |
| 18 | 121.0350828 | 14.64738321 |
| 19 | 123.8582127 | 10.31223214 |
| 20 | 121.0308051 | 14.65929261 |
| 21 | 121.0125751 | 14.42874196 |
| 22 | 121.0265459 | 14.39094848 |
| 23 | 121.0272516 | 14.66177014 |
| 24 | 121.0639547 | 14.2394838 |
| 25 | 123.7952983 | 10.31724588 |
| 26 | 121.0330756 | 14.64205712 |
| 27 | 121.0732049 | 14.25466135 |
| 28 | 123.8251368 | 10.34494141 |
| 29 | 123.8054298 | 10.31592916 |
| 30 | 120.9887098 | 14.44612893 |
| 31 | 121.0488945 | 14.23072357 |
| 32 | 121.0717828 | 14.25677277 |
| 33 | 123.8028315 | 10.32714818 |
| 34 | 121.0566622 | 14.6427324 |
%sql SELECT * FROM songs LIMIT 100;
* postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 100 rows affected.
| song_id | song_title | release_date | year | duration_ms | acousticness | danceability | energy | explicit | instrumentalness | key | liveness | loudness | mode | popularity | speechiness | tempo | valence |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 3MoV1UsAJmz64LHqyiRMp0 | Say My Name | 2018-09-14 00:00:00 | 2018 | 198947 | 0.137 | 0.703 | 0.66 | 0 | 0.0 | 10 | 0.192 | -5.119 | 0 | 66 | 0.123 | 95.031 | 0.79 |
| 5FlmoOiXokZHJvESrFbsyg | MAMA | 2018-11-27 00:00:00 | 2018 | 192405 | 0.0893 | 0.761 | 0.672 | 1 | 9.77e-06 | 1 | 0.14 | -5.887 | 1 | 68 | 0.212 | 160.077 | 0.437 |
| 5x7Fb9wMOtxd7eZzQ9EzVd | Gone Girl | 2018-04-27 00:00:00 | 2018 | 163453 | 0.287 | 0.527 | 0.868 | 0 | 0.0 | 2 | 0.0885 | -2.653 | 1 | 61 | 0.0416 | 172.086 | 0.77 |
| 5YeHLHDdQ4nKHk81XFWhCU | successful | 2018-08-17 00:00:00 | 2018 | 227387 | 0.107 | 0.847 | 0.603 | 1 | 7.4e-06 | 0 | 0.106 | -4.607 | 0 | 67 | 0.0397 | 114.045 | 0.735 |
| 66XDf77gBcfQKi4I2vIbTj | Waste It On Me | 2018-11-09 00:00:00 | 2018 | 192537 | 0.252 | 0.669 | 0.684 | 0 | 0.0 | 7 | 0.72 | -4.595 | 1 | 70 | 0.0585 | 96.097 | 0.466 |
| 1gewwJHPkvysxLEGgOKQuB | Why Not Me | 2018-07-01 00:00:00 | 2018 | 195000 | 0.48 | 0.905 | 0.721 | 0 | 0.0 | 7 | 0.528 | -6.922 | 1 | 64 | 0.31 | 145.958 | 0.695 |
| 2CY9aDyI5NQLSuvU2d2VzX | All Thee Above (feat. Kevin Gates) | 2018-10-12 00:00:00 | 2018 | 212400 | 0.00988 | 0.736 | 0.568 | 1 | 0.0 | 1 | 0.0731 | -5.973 | 1 | 61 | 0.135 | 145.93 | 0.514 |
| 1OKQIvaO7yn7R2BpdUFG7D | GET UP | 2018-05-04 00:00:00 | 2018 | 245787 | 0.0174 | 0.552 | 0.935 | 0 | 1.98e-06 | 0 | 0.116 | -4.096 | 1 | 63 | 0.0623 | 74.954 | 0.38 |
| 6PoKfDY78ih5OveWirggRX | Off White VLONE (Lil Baby & Gunna feat. Lil Durk & NAV) | 2018-10-05 00:00:00 | 2018 | 187465 | 0.105 | 0.859 | 0.506 | 1 | 0.0 | 10 | 0.113 | -8.115 | 0 | 63 | 0.418 | 140.973 | 0.153 |
| 27YD36FUikKxbp3bKSiKGZ | First Time (feat. Dylan Matthew) | 2018-10-12 00:00:00 | 2018 | 297782 | 0.251 | 0.422 | 0.677 | 0 | 3.55e-06 | 11 | 0.16 | -5.76 | 1 | 63 | 0.0365 | 150.131 | 0.146 |
| 7yB6GF5kgPEoeGE4EAWiJU | Blue Laces 2 | 2018-02-16 00:00:00 | 2018 | 250920 | 0.0276 | 0.569 | 0.836 | 1 | 0.0 | 4 | 0.479 | -3.831 | 0 | 62 | 0.226 | 75.701 | 0.0927 |
| 2Xc9MR2NMc5IKPFKaEjt1k | Hot Girl | 2018-12-21 00:00:00 | 2018 | 196282 | 0.000278 | 0.761 | 0.637 | 1 | 0.0 | 10 | 0.251 | -5.432 | 0 | 65 | 0.313 | 185.904 | 0.374 |
| 29ZZAShKhwRUuKULSNtxX6 | Switch | 2018-09-14 00:00:00 | 2018 | 207071 | 0.0409 | 0.66 | 0.658 | 1 | 0.00595 | 7 | 0.102 | -6.127 | 1 | 67 | 0.0387 | 176.701 | 0.153 |
| 3uwrczwM9A3xX63jlcRhIC | Cleaner Air | 2018-10-30 00:00:00 | 2018 | 128129 | 0.876 | 0.717 | 0.797 | 1 | 0.000694 | 7 | 0.0836 | -5.393 | 1 | 64 | 0.0368 | 77.492 | 0.849 |
| 09ts3GnICqYEU5PkQCpJK3 | Say Something (feat. Chris Stapleton) | 2018-01-25 00:00:00 | 2018 | 278893 | 0.103 | 0.707 | 0.632 | 0 | 1.09e-05 | 10 | 0.0841 | -7.031 | 1 | 60 | 0.0789 | 97.04 | 0.372 |
| 5mrOnabhXnvCf5vFF7NJhG | Joan of Arc | 2018-06-08 00:00:00 | 2018 | 145161 | 0.00375 | 0.915 | 0.668 | 1 | 0.00241 | 1 | 0.253 | -2.52 | 1 | 64 | 0.04 | 124.053 | 0.0387 |
| 2ulXW6DPrhTDt7ZqqYDdhu | Genie | 2018-06-08 00:00:00 | 2018 | 194792 | 0.286 | 0.598 | 0.605 | 1 | 0.0 | 7 | 0.0875 | -4.92 | 0 | 62 | 0.452 | 159.517 | 0.513 |
| 2fwkl5Z8ujDtAh6qxEA5x0 | Lil Bebe (feat. Lil Baby) - Remix | 2018-11-30 00:00:00 | 2018 | 174720 | 0.0481 | 0.856 | 0.485 | 1 | 0.0 | 5 | 0.108 | -5.519 | 0 | 65 | 0.0597 | 136.005 | 0.0929 |
| 41o2ydrj7Xm9Yt5odIBqq4 | Velvet Light | 2018-04-05 00:00:00 | 2018 | 141587 | 0.75 | 0.67 | 0.419 | 0 | 0.135 | 6 | 0.0976 | -10.523 | 0 | 63 | 0.0751 | 77.919 | 0.513 |
| 6q8Lb50EtqNeeJXXe8mMAH | Culpables | 2018-09-14 00:00:00 | 2018 | 226627 | 0.279 | 0.728 | 0.801 | 0 | 4.71e-06 | 8 | 0.143 | -2.942 | 0 | 69 | 0.0683 | 130.059 | 0.416 |
| 1uqjLVaSyTkdDvfGAmXrHo | A Través Del Vaso | 2018-08-31 00:00:00 | 2018 | 180120 | 0.621 | 0.649 | 0.529 | 0 | 0.0 | 2 | 0.0563 | -4.895 | 0 | 65 | 0.0398 | 144.869 | 0.964 |
| 2YVt7k1qZXFhzYDe8FMDW6 | No Mentions | 2018-09-21 00:00:00 | 2018 | 220260 | 0.27 | 0.803 | 0.54 | 1 | 0.0 | 1 | 0.0819 | -6.862 | 0 | 61 | 0.318 | 139.989 | 0.345 |
| 4k0ZX0KlMBEOlTp5vexIrT | Bottle Service | 2018-05-04 00:00:00 | 2018 | 156837 | 0.113 | 0.849 | 0.582 | 1 | 0.0 | 8 | 0.152 | -6.407 | 1 | 62 | 0.214 | 100.049 | 0.516 |
| 07sa7qkxpemze5BBE7jufs | Dance with Me (feat. Kelsea Ballerini) | 2018-10-12 00:00:00 | 2018 | 199501 | 0.481 | 0.611 | 0.519 | 0 | 4.82e-06 | 1 | 0.107 | -9.854 | 1 | 63 | 0.035 | 147.981 | 0.462 |
| 3VzULnaqPKf1deQyZ5W4Ah | 1980s Horror Film II | 2018-10-11 00:00:00 | 2018 | 235272 | 0.0038 | 0.593 | 0.658 | 0 | 0.0126 | 2 | 0.0804 | -4.957 | 1 | 65 | 0.0304 | 116.021 | 0.27 |
| 0laoKkCyhp5HW3wGejhPV5 | En Menos de Un Minuto | 2018-11-16 00:00:00 | 2018 | 177714 | 0.554 | 0.702 | 0.621 | 1 | 0.0 | 7 | 0.226 | -6.884 | 1 | 65 | 0.0296 | 114.532 | 0.581 |
| 2J84DvAAI8ph5DYojk3HPt | GONE (feat. Trippie Redd) | 2018-04-25 00:00:00 | 2018 | 190893 | 0.00107 | 0.712 | 0.731 | 1 | 0.0 | 9 | 0.219 | -4.555 | 1 | 64 | 0.0335 | 118.016 | 0.876 |
| 739vCwA3EpBSkk3uDsI2wB | Lemon (feat. Drake) - Drake Remix | 2018-03-18 00:00:00 | 2018 | 226107 | 0.000738 | 0.777 | 0.657 | 1 | 3.34e-06 | 1 | 0.0932 | -6.434 | 1 | 64 | 0.237 | 189.992 | 0.245 |
| 1YmF9PvLhIISIANoMLIYGq | A$AP Forever (feat. Moby) | 2018-04-05 00:00:00 | 2018 | 233061 | 0.224 | 0.469 | 0.78 | 1 | 0.0 | 10 | 0.13 | -6.49 | 0 | 67 | 0.122 | 125.999 | 0.444 |
| 1IX47gefluXmKX4PrTBCRM | What is Love | 2018-07-09 00:00:00 | 2018 | 208240 | 0.0988 | 0.574 | 0.909 | 0 | 0.0 | 8 | 0.264 | -1.572 | 1 | 72 | 0.0655 | 169.964 | 0.565 |
| 0Sbeihd75SGitymHS8Bqbh | I Hate Love Songs | 2018-10-26 00:00:00 | 2018 | 191187 | 0.252 | 0.386 | 0.446 | 0 | 0.0 | 11 | 0.18 | -7.807 | 1 | 62 | 0.0297 | 179.995 | 0.4 |
| 7D8aQaRzoi9Qzz5yerVK5b | Throw A Fit | 2018-07-27 00:00:00 | 2018 | 158757 | 0.00597 | 0.888 | 0.586 | 1 | 0.0365 | 0 | 0.153 | -6.745 | 1 | 66 | 0.0604 | 150.051 | 0.401 |
| 4axYWgXASdsCBHuwInAubT | I don’t let go | 2018-12-07 00:00:00 | 2018 | 121630 | 0.591 | 0.797 | 0.293 | 1 | 0.0724 | 5 | 0.148 | -7.479 | 1 | 65 | 0.08 | 80.035 | 0.478 |
| 1746Hs7bQJMMYAZvVYM3ig | Two Shots (feat. gnash) | 2018-03-23 00:00:00 | 2018 | 179915 | 0.0542 | 0.768 | 0.408 | 1 | 0.0 | 4 | 0.106 | -8.551 | 0 | 61 | 0.0714 | 129.96 | 0.0905 |
| 5SO7pvSFiXwU0e1z7C4X7H | Alcohol You Later | 2018-12-14 00:00:00 | 2018 | 186600 | 0.0259 | 0.671 | 0.597 | 0 | 0.0 | 8 | 0.116 | -7.011 | 1 | 62 | 0.0351 | 100.039 | 0.422 |
| 5O9zs6G6RcB6yP1OKwnwiM | Choppa Won't Miss (feat. Young Thug) | 2018-05-11 00:00:00 | 2018 | 217960 | 0.0182 | 0.858 | 0.661 | 1 | 0.0 | 0 | 0.115 | -6.544 | 1 | 63 | 0.319 | 163.056 | 0.611 |
| 55GiOwtxNajXVbpjjIzMnD | SWISH | 2018-07-25 00:00:00 | 2018 | 194936 | 0.0831 | 0.884 | 0.541 | 1 | 0.0 | 11 | 0.118 | -5.608 | 1 | 70 | 0.221 | 105.009 | 0.274 |
| 0chpIFSjgfd1CPrAxM7PWp | No Rest | 2018-08-13 00:00:00 | 2018 | 135535 | 0.0186 | 0.655 | 0.498 | 1 | 4.47e-06 | 9 | 0.139 | -13.147 | 1 | 64 | 0.0479 | 162.861 | 0.508 |
| 5awvelCGpDQHwgZem0ira9 | Lately | 2018-11-01 00:00:00 | 2018 | 207000 | 0.213 | 0.718 | 0.632 | 0 | 1.28e-05 | 0 | 0.283 | -4.011 | 1 | 64 | 0.145 | 160.039 | 0.859 |
| 0h31TZMlv0ZLc5yppKngwk | Memories Are Made Of | 2018-06-01 00:00:00 | 2018 | 216667 | 0.0159 | 0.694 | 0.718 | 0 | 1.61e-06 | 2 | 0.287 | -6.188 | 1 | 62 | 0.0322 | 121.078 | 0.364 |
| 46Lx5epWOQBA3J86ovnxmV | Workin Out | 2018-11-26 00:00:00 | 2018 | 226859 | 0.384 | 0.818 | 0.57 | 1 | 0.000102 | 10 | 0.19 | -8.067 | 0 | 64 | 0.285 | 126.938 | 0.561 |
| 72gv4zhNvRVdQA0eOenCal | Symphony (feat. Zara Larsson) | 2018-11-30 00:00:00 | 2018 | 212733 | 0.239 | 0.715 | 0.605 | 0 | 1.41e-05 | 0 | 0.189 | -5.128 | 0 | 74 | 0.0428 | 122.956 | 0.454 |
| 0G2wimhVoDYXbQ6csDxtSf | Sex n' Drugs | 2018-02-09 00:00:00 | 2018 | 217778 | 0.299 | 0.831 | 0.32 | 0 | 0.0 | 7 | 0.0757 | -7.916 | 1 | 65 | 0.143 | 80.935 | 0.319 |
| 6OpU3cvY19M2hFxTbLKy5L | Juno | 2018-08-03 00:00:00 | 2018 | 231681 | 0.921 | 0.442 | 0.267 | 1 | 0.00026 | 7 | 0.148 | -12.857 | 1 | 63 | 0.0365 | 120.075 | 0.116 |
| 42k9e2hgl98IdgkXLg4pxs | No More (feat. Travis Scott, Kodak Black & 21 Savage) | 2018-11-06 00:00:00 | 2018 | 265120 | 0.413 | 0.636 | 0.501 | 1 | 0.0 | 11 | 0.339 | -7.26 | 0 | 65 | 0.329 | 82.309 | 0.196 |
| 1ZLrDPgR7mvuTco3rQK8Pk | Way Back Home (feat. Conor Maynard) - Sam Feldt Edit | 2018-12-21 00:00:00 | 2018 | 192453 | 0.269 | 0.737 | 0.747 | 0 | 0.0 | 10 | 0.219 | -4.818 | 1 | 75 | 0.0323 | 105.943 | 0.447 |
| 6PuoZT4kgw5DrUEdnQ6e01 | This Must Be The Place | 2018-01-12 00:00:00 | 2018 | 219261 | 0.349 | 0.886 | 0.371 | 0 | 0.00138 | 5 | 0.0985 | -10.542 | 1 | 63 | 0.0465 | 115.026 | 0.928 |
| 1T3E8z6VMXSsACrBSQHTkN | MyBoi - TroyBoi Remix | 2018-03-09 00:00:00 | 2018 | 211304 | 0.00626 | 0.879 | 0.58 | 0 | 0.0433 | 2 | 0.0653 | -4.797 | 1 | 68 | 0.0883 | 92.004 | 0.312 |
| 6nVm313QmsPlNllntTart1 | Living Hope | 2018-08-03 00:00:00 | 2018 | 327000 | 0.0304 | 0.211 | 0.558 | 0 | 0.0 | 3 | 0.0978 | -6.232 | 1 | 61 | 0.041 | 143.452 | 0.317 |
| 6XyxCBp6x3jvtxXvMN5sAA | 1539 N. Calvert | 2018-01-19 00:00:00 | 2018 | 157259 | 0.278 | 0.863 | 0.717 | 1 | 0.0826 | 4 | 0.0991 | -3.143 | 0 | 64 | 0.17 | 152.05 | 0.357 |
| 6VS7wKwtvL2FvTupYSWZ9e | Invincible | 2018-12-14 00:00:00 | 2018 | 196387 | 0.141 | 0.724 | 0.6 | 0 | 0.000218 | 10 | 0.104 | -4.391 | 0 | 65 | 0.29 | 86.984 | 0.466 |
| 1hHe7cirScEJWgAS0JFqTL | 7 Am Freestyle | 2018-10-19 00:00:00 | 2018 | 192907 | 0.0185 | 0.951 | 0.401 | 1 | 0.0 | 0 | 0.149 | -8.92 | 0 | 63 | 0.411 | 129.976 | 0.539 |
| 1jfBIylYmLMklQOpbWT5s0 | Blame It On The Kids | 2018-11-16 00:00:00 | 2018 | 196341 | 0.364 | 0.719 | 0.44 | 0 | 0.0 | 6 | 0.163 | -6.266 | 0 | 67 | 0.166 | 133.013 | 0.505 |
| 5P8lyudWE7HQxb4ludLbEm | Renai Circulation | 2018-11-02 00:00:00 | 2018 | 255040 | 0.0524 | 0.814 | 0.785 | 0 | 0.00111 | 1 | 0.111 | -5.679 | 0 | 68 | 0.0396 | 120.009 | 0.941 |
| 3z1QH1O1h15lFROjcECqgi | A Long Way | 2018-06-01 00:00:00 | 2018 | 217280 | 0.0194 | 0.483 | 0.713 | 0 | 0.000151 | 2 | 0.316 | -5.027 | 1 | 62 | 0.0295 | 161.905 | 0.594 |
| 2wRkBumdItthjYP9XknImg | Est√° Rico | 2018-09-28 00:00:00 | 2018 | 215151 | 0.266 | 0.817 | 0.74 | 0 | 0.0 | 11 | 0.0992 | -4.566 | 0 | 69 | 0.283 | 130.04 | 0.674 |
| 2vHfabj6nFebekTYODqntl | Want You Back | 2018-06-15 00:00:00 | 2018 | 173082 | 0.00699 | 0.618 | 0.867 | 1 | 1.03e-06 | 0 | 0.202 | -4.915 | 1 | 68 | 0.0439 | 98.03 | 0.579 |
| 45WkcBtgPS8xulUwEg63X1 | TOGETHER | 2018-08-15 00:00:00 | 2018 | 214707 | 0.469 | 0.622 | 0.375 | 1 | 1.28e-05 | 4 | 0.185 | -8.115 | 0 | 63 | 0.208 | 132.887 | 0.357 |
| 6xqbec9MHRmW7qtEINAlRx | Darte Remix | 2018-07-27 00:00:00 | 2018 | 512093 | 0.144 | 0.81 | 0.631 | 1 | 1.32e-06 | 4 | 0.451 | -6.541 | 0 | 70 | 0.0938 | 154.976 | 0.573 |
| 7f4bRMqRKRfUNPW2gbTHSt | Save Me From Myself (with NoMBe & Big Gigantic) | 2018-10-26 00:00:00 | 2018 | 211686 | 0.0198 | 0.673 | 0.676 | 0 | 1.26e-05 | 4 | 0.428 | -4.888 | 0 | 62 | 0.216 | 82.979 | 0.391 |
| 1LV6zxUFIUyePwsdFHNIYA | El de la Gorra 27 | 2018-04-20 00:00:00 | 2018 | 200071 | 0.781 | 0.663 | 0.785 | 0 | 2.33e-06 | 11 | 0.113 | -3.874 | 0 | 67 | 0.0365 | 107.455 | 0.84 |
| 3FskQrDXcY24ur2fCvz35O | Ye | 2018-01-26 00:00:00 | 2018 | 231618 | 0.0181 | 0.503 | 0.592 | 1 | 0.00014 | 5 | 0.169 | -5.923 | 0 | 72 | 0.444 | 201.85 | 0.344 |
| 3TgelNfEK1DnYrsHOBxaVJ | El De Los Lentes Gucci | 2018-07-10 00:00:00 | 2018 | 125412 | 0.37 | 0.765 | 0.539 | 0 | 0.0 | 11 | 0.039 | -5.028 | 0 | 68 | 0.0437 | 99.94 | 0.965 |
| 1D23GEeQ32aqLw5ZnY3Ivp | COOLEST MONKEY IN THE JUNGLE | 2018-05-11 00:00:00 | 2018 | 152213 | 0.324 | 0.893 | 0.676 | 1 | 0.0 | 9 | 0.0968 | -4.867 | 0 | 63 | 0.227 | 125.923 | 0.804 |
| 4BiiOzZCrXEzHRLYcYFiD5 | Hope | 2018-12-14 00:00:00 | 2018 | 180120 | 0.488 | 0.773 | 0.699 | 0 | 3.6e-06 | 1 | 0.0814 | -5.982 | 0 | 71 | 0.0958 | 104.941 | 0.513 |
| 6e13443Ve7RGcAUScTgYtl | The Last Of The Real Ones | 2018-01-19 00:00:00 | 2018 | 230400 | 0.00599 | 0.576 | 0.872 | 0 | 0.0 | 8 | 0.188 | -5.293 | 1 | 63 | 0.0471 | 115.042 | 0.559 |
| 2yI8omCrTAyrTcK7NRppu7 | Ocean (feat. Jacquees) | 2018-06-22 00:00:00 | 2018 | 219437 | 0.268 | 0.709 | 0.264 | 1 | 0.0 | 1 | 0.24 | -17.696 | 0 | 62 | 0.186 | 133.858 | 0.341 |
| 2lRe5wBRm4xaSKTbDn2vLD | Trippy (feat. J. Cole) | 2018-11-16 00:00:00 | 2018 | 323587 | 0.0707 | 0.57 | 0.65 | 1 | 0.0 | 2 | 0.339 | -8.538 | 1 | 65 | 0.326 | 96.767 | 0.174 |
| 1j8gLQNfFOI0vs8qcVTWwa | ¿Por Qué Cambiaste De Opinión? | 2018-07-20 00:00:00 | 2018 | 241907 | 0.446 | 0.735 | 0.49 | 0 | 3.52e-06 | 5 | 0.129 | -4.937 | 1 | 69 | 0.0299 | 135.159 | 0.89 |
| 4n1bdaKwynQndm47x5HqWX | Blue Tint | 2018-06-29 00:00:00 | 2018 | 162800 | 0.013 | 0.888 | 0.372 | 1 | 0.0 | 7 | 0.184 | -10.254 | 0 | 64 | 0.358 | 133.998 | 0.123 |
| 2u6XJ2PPaCyyRJqC0x6TMH | Through the Storm | 2018-06-08 00:00:00 | 2018 | 163567 | 0.631 | 0.881 | 0.581 | 1 | 0.0 | 5 | 0.0986 | -5.162 | 1 | 61 | 0.0901 | 78.508 | 0.876 |
| 6euzJRzwBig3NESMppkxOK | All I Got | 2018-02-16 00:00:00 | 2018 | 218704 | 0.12 | 0.667 | 0.509 | 0 | 1.91e-06 | 1 | 0.269 | -6.584 | 1 | 64 | 0.0348 | 104.995 | 0.289 |
| 4uTFofZ26M2VgCVq3ixUUY | Haunt U | 2018-02-24 00:00:00 | 2018 | 100260 | 0.393 | 0.463 | 0.632 | 1 | 0.0 | 7 | 0.127 | -9.577 | 1 | 68 | 0.0525 | 163.71 | 0.0894 |
| 4ToogKQq58R9TvO40qRuxi | I Wanna Know | 2018-07-27 00:00:00 | 2018 | 206727 | 0.501 | 0.432 | 0.85 | 0 | 6.59e-06 | 10 | 0.203 | -4.417 | 1 | 63 | 0.0771 | 82.607 | 0.109 |
| 1KnJwmgLyBfB7u920qLbEq | Romance Garbage | 2018-07-07 00:00:00 | 2018 | 122070 | 0.152 | 0.467 | 0.553 | 0 | 0.683 | 6 | 0.291 | -9.692 | 0 | 64 | 0.1 | 144.943 | 0.39 |
| 0FbSpY8RPxTRxi1Fpj9Dz9 | Booty | 2018-02-27 00:00:00 | 2018 | 160038 | 0.000831 | 0.938 | 0.549 | 1 | 0.000243 | 1 | 0.082 | -8.368 | 1 | 62 | 0.098 | 144.005 | 0.412 |
| 08zAFXhfyIxq9359NHksdP | Do Not Disturb (feat. Lil Yachty & Offset) | 2018-04-13 00:00:00 | 2018 | 157557 | 0.203 | 0.878 | 0.647 | 1 | 0.00185 | 10 | 0.335 | -4.566 | 0 | 64 | 0.0659 | 131.027 | 0.212 |
| 72B1Omo2kmEst4YKO8vOB6 | Scars | 2018-03-02 00:00:00 | 2018 | 238307 | 0.048 | 0.417 | 0.524 | 0 | 0.0 | 10 | 0.103 | -6.6 | 1 | 62 | 0.0326 | 75.266 | 0.152 |
| 2SPEg45ZaUFJCg9GwNmRvw | None Of My Business | 2018-10-19 00:00:00 | 2018 | 184005 | 0.428 | 0.793 | 0.488 | 0 | 0.0 | 2 | 0.0823 | -5.807 | 0 | 68 | 0.0462 | 133.957 | 0.5 |
| 3ZsLuJ8uU0lE6rGTkDaqEg | Alone Together | 2018-06-22 00:00:00 | 2018 | 171440 | 0.00527 | 0.541 | 0.87 | 0 | 0.0 | 1 | 0.0867 | -4.741 | 1 | 62 | 0.122 | 179.975 | 0.665 |
| 2TLDilmv1UmRTfyb7f2Hrx | One That Got Away | 2018-06-01 00:00:00 | 2018 | 218733 | 0.00303 | 0.565 | 0.825 | 0 | 0.0 | 10 | 0.0779 | -4.245 | 1 | 61 | 0.0417 | 160.003 | 0.609 |
| 4qjLvvBh5ZeKEPyShKRf06 | Narcissist (feat. The 1975) | 2018-08-28 00:00:00 | 2018 | 195502 | 0.218 | 0.543 | 0.682 | 1 | 1.53e-06 | 3 | 0.085 | -5.81 | 1 | 67 | 0.113 | 179.969 | 0.424 |
| 41a7dZcq30Ss5kPMayWRV0 | That’s How You Feel | 2018-06-29 00:00:00 | 2018 | 157605 | 0.117 | 0.85 | 0.473 | 1 | 0.0105 | 10 | 0.108 | -5.747 | 0 | 65 | 0.209 | 85.454 | 0.38 |
| 48x8fBWfyeSMzLl5gbsjr9 | COME THRU | 2018-10-26 00:00:00 | 2018 | 153169 | 0.163 | 0.66 | 0.596 | 1 | 0.86 | 0 | 0.109 | -8.207 | 1 | 65 | 0.0358 | 77.026 | 0.0757 |
| 1FDV80LC5JnTDAEjPDovER | Worthy Of You | 2018-12-06 00:00:00 | 2018 | 190619 | 0.439 | 0.451 | 0.452 | 0 | 0.0 | 1 | 0.478 | -9.363 | 0 | 66 | 0.0574 | 57.523 | 0.386 |
| 33DzKnwuBE6lfOiADwzd5E | TOOTIMETOOTIMETOOTIME | 2018-11-30 00:00:00 | 2018 | 207851 | 0.177 | 0.667 | 0.824 | 0 | 0.0152 | 8 | 0.0933 | -5.867 | 1 | 68 | 0.0665 | 114.998 | 0.518 |
| 1x3W8RZxW94lrVGhP95qA6 | You Are The Reason - Duet Version | 2018-11-30 00:00:00 | 2018 | 190760 | 0.382 | 0.403 | 0.356 | 0 | 0.0 | 10 | 0.148 | -7.547 | 1 | 72 | 0.0298 | 171.454 | 0.151 |
| 3lWzVNe1yFZlkeBBzUuZYu | Bella Ciao - M√∫sica Original de la Serie la Casa de Papel/ Money Heist | 2018-04-20 00:00:00 | 2018 | 139845 | 0.538 | 0.46 | 0.564 | 0 | 0.0 | 9 | 0.191 | -10.329 | 0 | 78 | 0.0343 | 135.178 | 0.962 |
| 18xmRkPWE0sGUzFyNNAAhN | Dying in LA | 2018-06-22 00:00:00 | 2018 | 229053 | 0.876 | 0.346 | 0.35 | 0 | 1.98e-06 | 2 | 0.0708 | -4.988 | 1 | 65 | 0.0328 | 126.444 | 0.315 |
| 6DuEfVcF8bmEkiFKr41SiM | Prayed For You - Acoustic | 2018-11-16 00:00:00 | 2018 | 205915 | 0.63 | 0.713 | 0.386 | 0 | 0.0 | 4 | 0.151 | -6.995 | 1 | 61 | 0.0291 | 139.968 | 0.52 |
| 5sWbwccBcyHsg5LEKWGZo9 | BRACKETS | 2018-04-20 00:00:00 | 2018 | 315771 | 0.19 | 0.675 | 0.567 | 1 | 1.77e-06 | 10 | 0.175 | -9.147 | 0 | 63 | 0.263 | 84.039 | 0.658 |
| 3BLKY8w5XTehYgLb3cFZgg | Deadman's Curve (Live) | 2018-06-29 00:00:00 | 2018 | 207453 | 0.731 | 0.392 | 0.496 | 0 | 0.0 | 2 | 0.908 | -9.664 | 1 | 60 | 0.203 | 172.243 | 0.604 |
| 6dJJT9h7FzjenIaa31bw8p | Yo Fui El Elegante | 2018-02-12 00:00:00 | 2018 | 146413 | 0.296 | 0.785 | 0.831 | 0 | 0.0 | 9 | 0.176 | -4.439 | 1 | 66 | 0.193 | 134.829 | 0.581 |
| 7IOxauZfx5l18mYfX0GZm6 | Knees | 2018-06-22 00:00:00 | 2018 | 206581 | 0.0781 | 0.777 | 0.577 | 0 | 1.02e-06 | 11 | 0.0869 | -6.872 | 0 | 68 | 0.0357 | 109.019 | 0.381 |
| 0r9d60ZYPAlFHq8qnUtkXN | Madura (feat. Bad Bunny) | 2018-04-13 00:00:00 | 2018 | 190909 | 0.138 | 0.878 | 0.555 | 0 | 1.34e-06 | 1 | 0.151 | -7.639 | 0 | 70 | 0.202 | 88.029 | 0.751 |
| 645YBsxhFPSOKxGep3uHLv | Love Maze | 2018-05-18 00:00:00 | 2018 | 221529 | 0.0487 | 0.636 | 0.876 | 0 | 0.0 | 9 | 0.0728 | -4.652 | 1 | 70 | 0.0617 | 149.932 | 0.759 |
| 6Z7m5DLNPOaPg9T6nXK0rQ | DNA | 2018-08-24 00:00:00 | 2018 | 223123 | 0.0223 | 0.599 | 0.774 | 0 | 0.0 | 1 | 0.0669 | -4.014 | 0 | 72 | 0.0541 | 129.819 | 0.696 |
| 48XfojJC87VKLlUotYV0Gm | The Truth Untold | 2018-05-18 00:00:00 | 2018 | 242293 | 0.372 | 0.57 | 0.422 | 0 | 0.0 | 1 | 0.127 | -7.561 | 0 | 70 | 0.0422 | 113.007 | 0.169 |
| 46za9pKXWkesH9DCrBbtNU | Low | 2018-04-28 00:00:00 | 2018 | 233963 | 0.689 | 0.746 | 0.438 | 1 | 0.103 | 4 | 0.126 | -7.016 | 0 | 64 | 0.0326 | 119.991 | 0.042 |
| 50Hv5NZIM0puIUoBttjpfb | King of the Clouds | 2018-06-22 00:00:00 | 2018 | 160747 | 0.237 | 0.285 | 0.922 | 0 | 0.0 | 2 | 0.159 | -1.932 | 0 | 64 | 0.247 | 198.063 | 0.472 |
%sql SELECT * FROM songs_artists LIMIT 100;
* postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 100 rows affected.
| artists | song_title |
|---|---|
| David Guetta | Say My Name |
| Bebe Rexha | Say My Name |
| J Balvin | Say My Name |
| 6ix9ine | MAMA |
| Nicki Minaj | MAMA |
| Kanye West | MAMA |
| Morgan Wallen | Gone Girl |
| Ariana Grande | successful |
| Steve Aoki | Waste It On Me |
| BTS | Waste It On Me |
| Forrest. | Why Not Me |
| Biskwiq | Why Not Me |
| Plies | All Thee Above (feat. Kevin Gates) |
| Kevin Gates | All Thee Above (feat. Kevin Gates) |
| Shinedown | GET UP |
| Lil Baby | Off White VLONE (Lil Baby & Gunna feat. Lil Durk & NAV) |
| Gunna | Off White VLONE (Lil Baby & Gunna feat. Lil Durk & NAV) |
| Lil Durk | Off White VLONE (Lil Baby & Gunna feat. Lil Durk & NAV) |
| NAV | Off White VLONE (Lil Baby & Gunna feat. Lil Durk & NAV) |
| Seven Lions | First Time (feat. Dylan Matthew) |
| SLANDER | First Time (feat. Dylan Matthew) |
| Dabin | First Time (feat. Dylan Matthew) |
| Dylan Matthew | First Time (feat. Dylan Matthew) |
| Nipsey Hussle | Blue Laces 2 |
| Megan Thee Stallion | Hot Girl |
| 6LACK | Switch |
| Yeek | Cleaner Air |
| Justin Timberlake | Say Something (feat. Chris Stapleton) |
| Chris Stapleton | Say Something (feat. Chris Stapleton) |
| Night Lovell | Joan of Arc |
| $uicideBoy$ | Joan of Arc |
| YoungBoy Never Broke Again | Genie |
| DaniLeigh | Lil Bebe (feat. Lil Baby) - Remix |
| Lil Baby | Lil Bebe (feat. Lil Baby) - Remix |
| Jakob Ogawa | Velvet Light |
| KAROL G | Culpables |
| Anuel AA | Culpables |
| Banda Los Sebastianes | A Través Del Vaso |
| YoungBoy Never Broke Again | No Mentions |
| Shoreline Mafia | Bottle Service |
| Morgan Evans | Dance with Me (feat. Kelsea Ballerini) |
| Kelsea Ballerini | Dance with Me (feat. Kelsea Ballerini) |
| Wallows | 1980s Horror Film II |
| T3R Elemento | En Menos de Un Minuto |
| Paris Shadows | GONE (feat. Trippie Redd) |
| Trippie Redd | GONE (feat. Trippie Redd) |
| N.E.R.D | Lemon (feat. Drake) - Drake Remix |
| Rihanna | Lemon (feat. Drake) - Drake Remix |
| Drake | Lemon (feat. Drake) - Drake Remix |
| A$AP Rocky | A$AP Forever (feat. Moby) |
| Moby | A$AP Forever (feat. Moby) |
| TWICE | What is Love |
| Kelsea Ballerini | I Hate Love Songs |
| Tinashe | Throw A Fit |
| XXXTENTACION | I don’t let go |
| Goody Grace | Two Shots (feat. gnash) |
| gnash | Two Shots (feat. gnash) |
| Mitchell Tenpenny | Alcohol You Later |
| Playboi Carti | Choppa Won't Miss (feat. Young Thug) |
| Young Thug | Choppa Won't Miss (feat. Young Thug) |
| Tyga | SWISH |
| Lil Skies | No Rest |
| Forrest. | Lately |
| Biskwiq | Lately |
| Ryce | Lately |
| Luke Combs | Memories Are Made Of |
| JID | Workin Out |
| Clean Bandit | Symphony (feat. Zara Larsson) |
| Zara Larsson | Symphony (feat. Zara Larsson) |
| Abhi The Nomad | Sex n' Drugs |
| Harrison Sands | Sex n' Drugs |
| Copper King | Sex n' Drugs |
| Choker | Juno |
| Metro Boomin | No More (feat. Travis Scott, Kodak Black & 21 Savage) |
| Travis Scott | No More (feat. Travis Scott, Kodak Black & 21 Savage) |
| Kodak Black | No More (feat. Travis Scott, Kodak Black & 21 Savage) |
| 21 Savage | No More (feat. Travis Scott, Kodak Black & 21 Savage) |
| SHAUN | Way Back Home (feat. Conor Maynard) - Sam Feldt Edit |
| Conor Maynard | Way Back Home (feat. Conor Maynard) - Sam Feldt Edit |
| Sam Feldt | Way Back Home (feat. Conor Maynard) - Sam Feldt Edit |
| Sure Sure | This Must Be The Place |
| Billie Eilish | MyBoi - TroyBoi Remix |
| TroyBoi | MyBoi - TroyBoi Remix |
| Phil Wickham | Living Hope |
| JPEGMAFIA | 1539 N. Calvert |
| Aminé | Invincible |
| Future | 7 Am Freestyle |
| Juice WRLD | 7 Am Freestyle |
| AViVA | Blame It On The Kids |
| 物語シリーズ | Renai Circulation |
| Luke Combs | A Long Way |
| Marc Anthony | Est√° Rico |
| Will Smith | Est√° Rico |
| Bad Bunny | Est√° Rico |
| 5 Seconds of Summer | Want You Back |
| Aminé | TOGETHER |
| Alex Rose | Darte Remix |
| Casper Magico | Darte Remix |
| Nengo Flow | Darte Remix |
| Bryant Myers | Darte Remix |
%sql SELECT * FROM users LIMIT 100;
* postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 66 rows affected.
| userid | userfullname | gender | level |
|---|---|---|---|
| 1 | Adelyn Jordan | F | free |
| 2 | Agnes Lazo | F | paid |
| 3 | Alyssa Fuentebella | F | free |
| 4 | Amos Changchoco | M | paid |
| 5 | Angelo Santiago | M | paid |
| 6 | Anna Ramos | F | paid |
| 7 | Argel Alvezo | M | paid |
| 8 | Bianca Cirio | F | paid |
| 9 | Billy Alegrado | M | paid |
| 10 | Bonnie Ruiz | F | paid |
| 11 | Chris Soriano | M | paid |
| 12 | Cid Ual | M | free |
| 13 | Dale Garcia | M | paid |
| 14 | Dan Nuesca | M | free |
| 15 | Donna Magugat | F | paid |
| 16 | Elizabeth Brooks | F | free |
| 17 | Emily Benson | F | paid |
| 18 | Fred Rara | M | paid |
| 19 | Gab Cadena | M | paid |
| 20 | Gelo Timajo | M | paid |
| 21 | Gil Uy | F | paid |
| 22 | Hayden Brock | F | paid |
| 23 | HJ Bayaborda | M | free |
| 24 | Jaffy Tan | M | paid |
| 25 | Jazel Jayme | F | free |
| 26 | Jill Aiken | F | free |
| 27 | Joaqs Gonzales | M | free |
| 28 | John Gacal | M | free |
| 29 | Julia Las | F | free |
| 30 | K-Ann Carandang | F | paid |
| 31 | Karen Reyes | F | paid |
| 32 | Ken Inocencio | M | free |
| 33 | Kimberly Hohl | F | paid |
| 34 | Lauren Frank | F | free |
| 35 | Law Guidote | M | paid |
| 36 | Lily Cooper | F | paid |
| 37 | Magdalene Herman | F | paid |
| 38 | Mandy Mapa | M | paid |
| 39 | Manu Gaspar | M | free |
| 40 | Marc Castro | M | free |
| 41 | Margs Baluyut | F | free |
| 42 | Mark Acot | M | paid |
| 43 | Marvee dela Resma | M | paid |
| 44 | Mason Hao | M | paid |
| 45 | Mavel Paderes | F | free |
| 46 | Miguel Pingol | M | paid |
| 47 | Mike Tanjango | M | free |
| 48 | Mimay Rodrigo | F | paid |
| 49 | Nancy Cruz | F | paid |
| 50 | Norman Lapid | M | paid |
| 51 | Pao de Guzman | M | paid |
| 52 | Paolo Punzalan | M | paid |
| 53 | Pat Donato | F | paid |
| 54 | Patrick La Rosa | M | paid |
| 55 | Patsy Hanlon | F | paid |
| 56 | Pauline Guevarra | F | free |
| 57 | Sharyl Sy | F | paid |
| 58 | Stacey Crosby | F | paid |
| 59 | Susan Boyer | F | paid |
| 60 | Toby Fernando | M | paid |
| 61 | Ven Velante | M | free |
| 62 | Vince Rivera | M | paid |
| 63 | Viola Conner | F | paid |
| 64 | Weddy Diamada | M | paid |
| 65 | Xidy del Rosario | F | paid |
| 66 | Zhoya Ginez | F | free |
pw = getpass.getpass()
%sql postgresql://songmanager:$pw@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb
'Connected: songmanager@songsdb'
%sql \d
postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database * postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 6 rows affected.
| schema | name | type | owner |
|---|---|---|---|
| public | dim_artists | table | songmanager |
| public | dim_dates | table | songmanager |
| public | dim_locations | table | songmanager |
| public | dim_songs | table | songmanager |
| public | dim_users | table | songmanager |
| public | fact_listens | table | songmanager |
%sql \dt
postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database * postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 6 rows affected.
| schema | name | type | owner |
|---|---|---|---|
| public | dim_artists | table | songmanager |
| public | dim_dates | table | songmanager |
| public | dim_locations | table | songmanager |
| public | dim_songs | table | songmanager |
| public | dim_users | table | songmanager |
| public | fact_listens | table | songmanager |
%sql SELECT COUNT (*) FROM dim_artists;
postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database * postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 1 rows affected.
| count |
|---|
| 10942 |
%sql SELECT COUNT (*) FROM dim_dates;
postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database * postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 1 rows affected.
| count |
|---|
| 11469 |
%sql SELECT COUNT (*) FROM dim_locations;
postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database * postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 1 rows affected.
| count |
|---|
| 1518 |
%sql SELECT COUNT (*) FROM dim_songs;
postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database * postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 1 rows affected.
| count |
|---|
| 2000 |
%sql SELECT COUNT (*) FROM dim_users;
postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database * postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 1 rows affected.
| count |
|---|
| 528 |
%sql SELECT COUNT (*) FROM fact_listens;
postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database * postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 1 rows affected.
| count |
|---|
| 2841 |
%sql SELECT * FROM dim_artists LIMIT 100;
postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database * postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 100 rows affected.
| artist_id | artist |
|---|---|
| 1 | David Guetta |
| 2 | Bebe Rexha |
| 3 | J Balvin |
| 4 | 6ix9ine |
| 5 | Nicki Minaj |
| 6 | Kanye West |
| 7 | Morgan Wallen |
| 8 | Ariana Grande |
| 9 | Steve Aoki |
| 10 | BTS |
| 11 | Forrest. |
| 12 | Biskwiq |
| 13 | Plies |
| 14 | Kevin Gates |
| 15 | Shinedown |
| 16 | Lil Baby |
| 17 | Gunna |
| 18 | Lil Durk |
| 19 | NAV |
| 20 | Seven Lions |
| 21 | SLANDER |
| 22 | Dabin |
| 23 | Dylan Matthew |
| 24 | Nipsey Hussle |
| 25 | Megan Thee Stallion |
| 26 | 6LACK |
| 27 | Yeek |
| 28 | Justin Timberlake |
| 29 | Chris Stapleton |
| 30 | Night Lovell |
| 31 | $uicideBoy$ |
| 32 | YoungBoy Never Broke Again |
| 33 | DaniLeigh |
| 34 | Lil Baby |
| 35 | Jakob Ogawa |
| 36 | KAROL G |
| 37 | Anuel AA |
| 38 | Banda Los Sebastianes |
| 39 | YoungBoy Never Broke Again |
| 40 | Shoreline Mafia |
| 41 | Morgan Evans |
| 42 | Kelsea Ballerini |
| 43 | Wallows |
| 44 | T3R Elemento |
| 45 | Paris Shadows |
| 46 | Trippie Redd |
| 47 | N.E.R.D |
| 48 | Rihanna |
| 49 | Drake |
| 50 | A$AP Rocky |
| 51 | Moby |
| 52 | TWICE |
| 53 | Kelsea Ballerini |
| 54 | Tinashe |
| 55 | XXXTENTACION |
| 56 | Goody Grace |
| 57 | gnash |
| 58 | Mitchell Tenpenny |
| 59 | Playboi Carti |
| 60 | Young Thug |
| 61 | Tyga |
| 62 | Lil Skies |
| 63 | Forrest. |
| 64 | Biskwiq |
| 65 | Ryce |
| 66 | Luke Combs |
| 67 | JID |
| 68 | Clean Bandit |
| 69 | Zara Larsson |
| 70 | Abhi The Nomad |
| 71 | Harrison Sands |
| 72 | Copper King |
| 73 | Choker |
| 74 | Metro Boomin |
| 75 | Travis Scott |
| 76 | Kodak Black |
| 77 | 21 Savage |
| 78 | SHAUN |
| 79 | Conor Maynard |
| 80 | Sam Feldt |
| 81 | Sure Sure |
| 82 | Billie Eilish |
| 83 | TroyBoi |
| 84 | Phil Wickham |
| 85 | JPEGMAFIA |
| 86 | Aminé |
| 87 | Future |
| 88 | Juice WRLD |
| 89 | AViVA |
| 90 | 物語シリーズ |
| 91 | Luke Combs |
| 92 | Marc Anthony |
| 93 | Will Smith |
| 94 | Bad Bunny |
| 95 | 5 Seconds of Summer |
| 96 | Aminé |
| 97 | Alex Rose |
| 98 | Casper Magico |
| 99 | Nengo Flow |
| 100 | Bryant Myers |
%sql SELECT * FROM dim_dates LIMIT 100;
postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database * postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 100 rows affected.
| date_id | year | month | day | hour | minute | second |
|---|---|---|---|---|---|---|
| 1 | 2018 | 11 | 1 | 20 | 57 | 10 |
| 2 | 2018 | 11 | 7 | 13 | 45 | 11 |
| 3 | 2018 | 11 | 14 | 6 | 26 | 17 |
| 4 | 2018 | 11 | 14 | 7 | 29 | 17 |
| 5 | 2018 | 11 | 14 | 23 | 22 | 14 |
| 6 | 2018 | 11 | 19 | 3 | 43 | 28 |
| 7 | 2018 | 11 | 1 | 21 | 1 | 46 |
| 8 | 2018 | 11 | 5 | 16 | 44 | 24 |
| 9 | 2018 | 11 | 7 | 0 | 1 | 16 |
| 10 | 2018 | 11 | 7 | 14 | 29 | 36 |
| 11 | 2018 | 11 | 8 | 10 | 34 | 27 |
| 12 | 2018 | 11 | 12 | 22 | 22 | 22 |
| 13 | 2018 | 11 | 14 | 7 | 1 | 5 |
| 14 | 2018 | 11 | 14 | 8 | 10 | 20 |
| 15 | 2018 | 11 | 1 | 21 | 2 | 12 |
| 16 | 2018 | 11 | 2 | 15 | 58 | 28 |
| 17 | 2018 | 11 | 2 | 16 | 38 | 59 |
| 18 | 2018 | 11 | 8 | 6 | 25 | 57 |
| 19 | 2018 | 11 | 8 | 9 | 45 | 43 |
| 20 | 2018 | 11 | 14 | 20 | 7 | 53 |
| 21 | 2018 | 11 | 1 | 21 | 8 | 16 |
| 22 | 2018 | 11 | 8 | 8 | 50 | 49 |
| 23 | 2018 | 11 | 14 | 7 | 32 | 16 |
| 24 | 2018 | 11 | 18 | 14 | 53 | 26 |
| 25 | 2018 | 11 | 19 | 2 | 53 | 22 |
| 26 | 2018 | 11 | 1 | 21 | 17 | 33 |
| 27 | 2018 | 11 | 2 | 13 | 1 | 57 |
| 28 | 2018 | 11 | 2 | 15 | 43 | 16 |
| 29 | 2018 | 11 | 2 | 16 | 27 | 56 |
| 30 | 2018 | 11 | 7 | 0 | 43 | 0 |
| 31 | 2018 | 11 | 7 | 15 | 56 | 48 |
| 32 | 2018 | 11 | 13 | 16 | 30 | 47 |
| 33 | 2018 | 11 | 13 | 17 | 29 | 55 |
| 34 | 2018 | 11 | 14 | 7 | 57 | 38 |
| 35 | 2018 | 11 | 19 | 10 | 20 | 0 |
| 36 | 2018 | 11 | 20 | 1 | 54 | 59 |
| 37 | 2018 | 11 | 20 | 2 | 36 | 47 |
| 38 | 2018 | 11 | 1 | 21 | 28 | 54 |
| 39 | 2018 | 11 | 3 | 16 | 24 | 53 |
| 40 | 2018 | 11 | 7 | 16 | 3 | 48 |
| 41 | 2018 | 11 | 14 | 22 | 40 | 13 |
| 42 | 2018 | 11 | 18 | 15 | 45 | 13 |
| 43 | 2018 | 11 | 20 | 9 | 25 | 53 |
| 44 | 2018 | 11 | 1 | 21 | 42 | 0 |
| 45 | 2018 | 11 | 2 | 12 | 35 | 41 |
| 46 | 2018 | 11 | 2 | 13 | 33 | 3 |
| 47 | 2018 | 11 | 2 | 16 | 22 | 13 |
| 48 | 2018 | 11 | 3 | 1 | 5 | 50 |
| 49 | 2018 | 11 | 10 | 0 | 29 | 18 |
| 50 | 2018 | 11 | 10 | 8 | 36 | 7 |
| 51 | 2018 | 11 | 14 | 6 | 36 | 13 |
| 52 | 2018 | 11 | 15 | 18 | 24 | 32 |
| 53 | 2018 | 11 | 15 | 20 | 38 | 46 |
| 54 | 2018 | 11 | 18 | 14 | 59 | 38 |
| 55 | 2018 | 11 | 18 | 15 | 12 | 45 |
| 56 | 2018 | 11 | 18 | 15 | 56 | 27 |
| 57 | 2018 | 11 | 1 | 21 | 50 | 15 |
| 58 | 2018 | 11 | 2 | 12 | 17 | 4 |
| 59 | 2018 | 11 | 2 | 13 | 6 | 37 |
| 60 | 2018 | 11 | 2 | 13 | 44 | 33 |
| 61 | 2018 | 11 | 4 | 6 | 35 | 26 |
| 62 | 2018 | 11 | 4 | 6 | 38 | 37 |
| 63 | 2018 | 11 | 14 | 8 | 14 | 44 |
| 64 | 2018 | 11 | 14 | 23 | 26 | 53 |
| 65 | 2018 | 11 | 15 | 19 | 46 | 21 |
| 66 | 2018 | 11 | 15 | 21 | 25 | 45 |
| 67 | 2018 | 11 | 20 | 7 | 18 | 5 |
| 68 | 2018 | 11 | 1 | 21 | 52 | 5 |
| 69 | 2018 | 11 | 3 | 17 | 17 | 26 |
| 70 | 2018 | 11 | 7 | 14 | 35 | 56 |
| 71 | 2018 | 11 | 13 | 16 | 51 | 11 |
| 72 | 2018 | 11 | 14 | 4 | 18 | 14 |
| 73 | 2018 | 11 | 14 | 6 | 15 | 56 |
| 74 | 2018 | 11 | 14 | 7 | 46 | 3 |
| 75 | 2018 | 11 | 14 | 21 | 53 | 46 |
| 76 | 2018 | 11 | 20 | 2 | 15 | 5 |
| 77 | 2018 | 11 | 20 | 11 | 3 | 59 |
| 78 | 2018 | 11 | 1 | 21 | 55 | 25 |
| 79 | 2018 | 11 | 2 | 12 | 9 | 30 |
| 80 | 2018 | 11 | 2 | 16 | 3 | 53 |
| 81 | 2018 | 11 | 15 | 20 | 37 | 12 |
| 82 | 2018 | 11 | 17 | 23 | 39 | 51 |
| 83 | 2018 | 11 | 18 | 13 | 39 | 14 |
| 84 | 2018 | 11 | 20 | 2 | 2 | 0 |
| 85 | 2018 | 11 | 1 | 22 | 23 | 14 |
| 86 | 2018 | 11 | 2 | 12 | 11 | 32 |
| 87 | 2018 | 11 | 3 | 15 | 52 | 36 |
| 88 | 2018 | 11 | 5 | 16 | 47 | 50 |
| 89 | 2018 | 11 | 7 | 5 | 32 | 6 |
| 90 | 2018 | 11 | 10 | 0 | 19 | 28 |
| 91 | 2018 | 11 | 14 | 22 | 37 | 59 |
| 92 | 2018 | 11 | 20 | 9 | 40 | 32 |
| 93 | 2018 | 11 | 20 | 9 | 48 | 40 |
| 94 | 2018 | 11 | 20 | 11 | 15 | 14 |
| 95 | 2018 | 11 | 20 | 13 | 42 | 19 |
| 96 | 2018 | 11 | 2 | 12 | 3 | 7 |
| 97 | 2018 | 11 | 3 | 17 | 19 | 54 |
| 98 | 2018 | 11 | 6 | 20 | 25 | 43 |
| 99 | 2018 | 11 | 7 | 0 | 52 | 29 |
| 100 | 2018 | 11 | 7 | 6 | 41 | 58 |
%sql SELECT * FROM dim_locations LIMIT 100;
postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database * postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 100 rows affected.
| user_id | longitude | latitude |
|---|---|---|
| 1 | 121.0577507 | 14.24777986 |
| 2 | 121.0244985 | 14.39202707 |
| 3 | 121.0513364 | 14.66512714 |
| 4 | 121.0323808 | 14.6287284 |
| 5 | 121.0546188 | 14.65372321 |
| 6 | 123.8736614 | 10.32727586 |
| 7 | 121.0598459 | 14.24851989 |
| 8 | 121.0553765 | 14.2407114 |
| 9 | 123.8694782 | 10.30912542 |
| 10 | 121.0327252 | 14.41835138 |
| 11 | 121.0377921 | 14.2298179 |
| 12 | 121.0364086 | 14.65605779 |
| 13 | 121.0450169 | 14.23488394 |
| 14 | 123.8524017 | 10.33690971 |
| 15 | 121.0709862 | 14.24565958 |
| 16 | 123.8014537 | 10.34546434 |
| 17 | 121.0614733 | 14.25084438 |
| 18 | 121.0350828 | 14.64738321 |
| 19 | 123.8582127 | 10.31223214 |
| 20 | 121.0308051 | 14.65929261 |
| 21 | 121.0125751 | 14.42874196 |
| 22 | 121.0265459 | 14.39094848 |
| 23 | 121.0272516 | 14.66177014 |
| 24 | 121.0639547 | 14.2394838 |
| 25 | 123.7952983 | 10.31724588 |
| 26 | 121.0330756 | 14.64205712 |
| 27 | 121.0732049 | 14.25466135 |
| 28 | 123.8251368 | 10.34494141 |
| 29 | 123.8054298 | 10.31592916 |
| 30 | 120.9887098 | 14.44612893 |
| 31 | 121.0488945 | 14.23072357 |
| 32 | 121.0717828 | 14.25677277 |
| 33 | 123.8028315 | 10.32714818 |
| 34 | 121.0566622 | 14.6427324 |
| 35 | 121.063723 | 14.24150137 |
| 36 | 123.8747406 | 10.3220519 |
| 37 | 121.0253021 | 14.38489132 |
| 38 | 123.8645445 | 10.31884932 |
| 39 | 121.0586424 | 14.23406803 |
| 40 | 121.0385185 | 14.39207613 |
| 41 | 121.0554547 | 14.24165886 |
| 42 | 121.029903 | 14.4049765 |
| 43 | 123.8328213 | 10.31070784 |
| 44 | 121.0056655 | 14.44359233 |
| 45 | 121.0756029 | 14.25468692 |
| 46 | 121.0593643 | 14.65222826 |
| 47 | 121.0398971 | 14.6628413 |
| 48 | 123.8737461 | 10.33265349 |
| 49 | 121.0474131 | 14.66397988 |
| 50 | 121.0345234 | 14.44554349 |
| 51 | 121.0414076 | 14.64044368 |
| 52 | 123.8708662 | 10.3218514 |
| 53 | 121.0464644 | 14.65888188 |
| 54 | 121.0288163 | 14.43105088 |
| 55 | 121.0158293 | 14.6647035 |
| 56 | 121.059258 | 14.6319537 |
| 57 | 121.0503601 | 14.64953264 |
| 58 | 121.0298833 | 14.40808912 |
| 59 | 121.0627138 | 14.23774099 |
| 60 | 121.017674 | 14.43148611 |
| 61 | 123.83282 | 10.30009076 |
| 62 | 121.0201485 | 14.3833865 |
| 63 | 121.0398209 | 14.43897897 |
| 64 | 121.0035655 | 14.44066566 |
| 65 | 121.0397764 | 14.44222001 |
| 66 | 121.0318088 | 14.62332026 |
| 1 | 121.0577507 | 14.24777986 |
| 2 | 121.0244985 | 14.39202707 |
| 3 | 121.0513364 | 14.66512714 |
| 4 | 121.0323808 | 14.6287284 |
| 5 | 121.0546188 | 14.65372321 |
| 6 | 123.8736614 | 10.32727586 |
| 7 | 121.0598459 | 14.24851989 |
| 8 | 121.0553765 | 14.2407114 |
| 9 | 123.8694782 | 10.30912542 |
| 10 | 121.0327252 | 14.41835138 |
| 11 | 121.0377921 | 14.2298179 |
| 12 | 121.0364086 | 14.65605779 |
| 13 | 121.0450169 | 14.23488394 |
| 14 | 123.8524017 | 10.33690971 |
| 15 | 121.0709862 | 14.24565958 |
| 16 | 123.8014537 | 10.34546434 |
| 17 | 121.0614733 | 14.25084438 |
| 18 | 121.0350828 | 14.64738321 |
| 19 | 123.8582127 | 10.31223214 |
| 20 | 121.0308051 | 14.65929261 |
| 21 | 121.0125751 | 14.42874196 |
| 22 | 121.0265459 | 14.39094848 |
| 23 | 121.0272516 | 14.66177014 |
| 24 | 121.0639547 | 14.2394838 |
| 25 | 123.7952983 | 10.31724588 |
| 26 | 121.0330756 | 14.64205712 |
| 27 | 121.0732049 | 14.25466135 |
| 28 | 123.8251368 | 10.34494141 |
| 29 | 123.8054298 | 10.31592916 |
| 30 | 120.9887098 | 14.44612893 |
| 31 | 121.0488945 | 14.23072357 |
| 32 | 121.0717828 | 14.25677277 |
| 33 | 123.8028315 | 10.32714818 |
| 34 | 121.0566622 | 14.6427324 |
%sql SELECT * FROM dim_songs LIMIT 100;
postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database * postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 100 rows affected.
| song_id | title | year | month | day | duration_ms | acousticness | danceability | energy | explicit | instrumentalness | key | liveness | loudness | mode | popularity | speechiness | tempo | valence |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 3MoV1UsAJmz64LHqyiRMp0 | Say My Name | 2018 | 9 | 14 | 198947 | 0.137 | 0.703 | 0.66 | 0 | 0.0 | 10 | 0.192 | -5.119 | 0 | 66 | 0.123 | 95.031 | 0.79 |
| 5FlmoOiXokZHJvESrFbsyg | MAMA | 2018 | 11 | 27 | 192405 | 0.0893 | 0.761 | 0.672 | 1 | 9.77e-06 | 1 | 0.14 | -5.887 | 1 | 68 | 0.212 | 160.077 | 0.437 |
| 5x7Fb9wMOtxd7eZzQ9EzVd | Gone Girl | 2018 | 4 | 27 | 163453 | 0.287 | 0.527 | 0.868 | 0 | 0.0 | 2 | 0.0885 | -2.653 | 1 | 61 | 0.0416 | 172.086 | 0.77 |
| 5YeHLHDdQ4nKHk81XFWhCU | successful | 2018 | 8 | 17 | 227387 | 0.107 | 0.847 | 0.603 | 1 | 7.4e-06 | 0 | 0.106 | -4.607 | 0 | 67 | 0.0397 | 114.045 | 0.735 |
| 66XDf77gBcfQKi4I2vIbTj | Waste It On Me | 2018 | 11 | 9 | 192537 | 0.252 | 0.669 | 0.684 | 0 | 0.0 | 7 | 0.72 | -4.595 | 1 | 70 | 0.0585 | 96.097 | 0.466 |
| 1gewwJHPkvysxLEGgOKQuB | Why Not Me | 2018 | 7 | 1 | 195000 | 0.48 | 0.905 | 0.721 | 0 | 0.0 | 7 | 0.528 | -6.922 | 1 | 64 | 0.31 | 145.958 | 0.695 |
| 2CY9aDyI5NQLSuvU2d2VzX | All Thee Above (feat. Kevin Gates) | 2018 | 10 | 12 | 212400 | 0.00988 | 0.736 | 0.568 | 1 | 0.0 | 1 | 0.0731 | -5.973 | 1 | 61 | 0.135 | 145.93 | 0.514 |
| 1OKQIvaO7yn7R2BpdUFG7D | GET UP | 2018 | 5 | 4 | 245787 | 0.0174 | 0.552 | 0.935 | 0 | 1.98e-06 | 0 | 0.116 | -4.096 | 1 | 63 | 0.0623 | 74.954 | 0.38 |
| 6PoKfDY78ih5OveWirggRX | Off White VLONE (Lil Baby & Gunna feat. Lil Durk & NAV) | 2018 | 10 | 5 | 187465 | 0.105 | 0.859 | 0.506 | 1 | 0.0 | 10 | 0.113 | -8.115 | 0 | 63 | 0.418 | 140.973 | 0.153 |
| 27YD36FUikKxbp3bKSiKGZ | First Time (feat. Dylan Matthew) | 2018 | 10 | 12 | 297782 | 0.251 | 0.422 | 0.677 | 0 | 3.55e-06 | 11 | 0.16 | -5.76 | 1 | 63 | 0.0365 | 150.131 | 0.146 |
| 7yB6GF5kgPEoeGE4EAWiJU | Blue Laces 2 | 2018 | 2 | 16 | 250920 | 0.0276 | 0.569 | 0.836 | 1 | 0.0 | 4 | 0.479 | -3.831 | 0 | 62 | 0.226 | 75.701 | 0.0927 |
| 2Xc9MR2NMc5IKPFKaEjt1k | Hot Girl | 2018 | 12 | 21 | 196282 | 0.000278 | 0.761 | 0.637 | 1 | 0.0 | 10 | 0.251 | -5.432 | 0 | 65 | 0.313 | 185.904 | 0.374 |
| 29ZZAShKhwRUuKULSNtxX6 | Switch | 2018 | 9 | 14 | 207071 | 0.0409 | 0.66 | 0.658 | 1 | 0.00595 | 7 | 0.102 | -6.127 | 1 | 67 | 0.0387 | 176.701 | 0.153 |
| 3uwrczwM9A3xX63jlcRhIC | Cleaner Air | 2018 | 10 | 30 | 128129 | 0.876 | 0.717 | 0.797 | 1 | 0.000694 | 7 | 0.0836 | -5.393 | 1 | 64 | 0.0368 | 77.492 | 0.849 |
| 09ts3GnICqYEU5PkQCpJK3 | Say Something (feat. Chris Stapleton) | 2018 | 1 | 25 | 278893 | 0.103 | 0.707 | 0.632 | 0 | 1.09e-05 | 10 | 0.0841 | -7.031 | 1 | 60 | 0.0789 | 97.04 | 0.372 |
| 5mrOnabhXnvCf5vFF7NJhG | Joan of Arc | 2018 | 6 | 8 | 145161 | 0.00375 | 0.915 | 0.668 | 1 | 0.00241 | 1 | 0.253 | -2.52 | 1 | 64 | 0.04 | 124.053 | 0.0387 |
| 2ulXW6DPrhTDt7ZqqYDdhu | Genie | 2018 | 6 | 8 | 194792 | 0.286 | 0.598 | 0.605 | 1 | 0.0 | 7 | 0.0875 | -4.92 | 0 | 62 | 0.452 | 159.517 | 0.513 |
| 2fwkl5Z8ujDtAh6qxEA5x0 | Lil Bebe (feat. Lil Baby) - Remix | 2018 | 11 | 30 | 174720 | 0.0481 | 0.856 | 0.485 | 1 | 0.0 | 5 | 0.108 | -5.519 | 0 | 65 | 0.0597 | 136.005 | 0.0929 |
| 41o2ydrj7Xm9Yt5odIBqq4 | Velvet Light | 2018 | 4 | 5 | 141587 | 0.75 | 0.67 | 0.419 | 0 | 0.135 | 6 | 0.0976 | -10.523 | 0 | 63 | 0.0751 | 77.919 | 0.513 |
| 6q8Lb50EtqNeeJXXe8mMAH | Culpables | 2018 | 9 | 14 | 226627 | 0.279 | 0.728 | 0.801 | 0 | 4.71e-06 | 8 | 0.143 | -2.942 | 0 | 69 | 0.0683 | 130.059 | 0.416 |
| 1uqjLVaSyTkdDvfGAmXrHo | A Través Del Vaso | 2018 | 8 | 31 | 180120 | 0.621 | 0.649 | 0.529 | 0 | 0.0 | 2 | 0.0563 | -4.895 | 0 | 65 | 0.0398 | 144.869 | 0.964 |
| 2YVt7k1qZXFhzYDe8FMDW6 | No Mentions | 2018 | 9 | 21 | 220260 | 0.27 | 0.803 | 0.54 | 1 | 0.0 | 1 | 0.0819 | -6.862 | 0 | 61 | 0.318 | 139.989 | 0.345 |
| 4k0ZX0KlMBEOlTp5vexIrT | Bottle Service | 2018 | 5 | 4 | 156837 | 0.113 | 0.849 | 0.582 | 1 | 0.0 | 8 | 0.152 | -6.407 | 1 | 62 | 0.214 | 100.049 | 0.516 |
| 07sa7qkxpemze5BBE7jufs | Dance with Me (feat. Kelsea Ballerini) | 2018 | 10 | 12 | 199501 | 0.481 | 0.611 | 0.519 | 0 | 4.82e-06 | 1 | 0.107 | -9.854 | 1 | 63 | 0.035 | 147.981 | 0.462 |
| 3VzULnaqPKf1deQyZ5W4Ah | 1980s Horror Film II | 2018 | 10 | 11 | 235272 | 0.0038 | 0.593 | 0.658 | 0 | 0.0126 | 2 | 0.0804 | -4.957 | 1 | 65 | 0.0304 | 116.021 | 0.27 |
| 0laoKkCyhp5HW3wGejhPV5 | En Menos de Un Minuto | 2018 | 11 | 16 | 177714 | 0.554 | 0.702 | 0.621 | 1 | 0.0 | 7 | 0.226 | -6.884 | 1 | 65 | 0.0296 | 114.532 | 0.581 |
| 2J84DvAAI8ph5DYojk3HPt | GONE (feat. Trippie Redd) | 2018 | 4 | 25 | 190893 | 0.00107 | 0.712 | 0.731 | 1 | 0.0 | 9 | 0.219 | -4.555 | 1 | 64 | 0.0335 | 118.016 | 0.876 |
| 739vCwA3EpBSkk3uDsI2wB | Lemon (feat. Drake) - Drake Remix | 2018 | 3 | 18 | 226107 | 0.000738 | 0.777 | 0.657 | 1 | 3.34e-06 | 1 | 0.0932 | -6.434 | 1 | 64 | 0.237 | 189.992 | 0.245 |
| 1YmF9PvLhIISIANoMLIYGq | A$AP Forever (feat. Moby) | 2018 | 4 | 5 | 233061 | 0.224 | 0.469 | 0.78 | 1 | 0.0 | 10 | 0.13 | -6.49 | 0 | 67 | 0.122 | 125.999 | 0.444 |
| 1IX47gefluXmKX4PrTBCRM | What is Love | 2018 | 7 | 9 | 208240 | 0.0988 | 0.574 | 0.909 | 0 | 0.0 | 8 | 0.264 | -1.572 | 1 | 72 | 0.0655 | 169.964 | 0.565 |
| 0Sbeihd75SGitymHS8Bqbh | I Hate Love Songs | 2018 | 10 | 26 | 191187 | 0.252 | 0.386 | 0.446 | 0 | 0.0 | 11 | 0.18 | -7.807 | 1 | 62 | 0.0297 | 179.995 | 0.4 |
| 7D8aQaRzoi9Qzz5yerVK5b | Throw A Fit | 2018 | 7 | 27 | 158757 | 0.00597 | 0.888 | 0.586 | 1 | 0.0365 | 0 | 0.153 | -6.745 | 1 | 66 | 0.0604 | 150.051 | 0.401 |
| 4axYWgXASdsCBHuwInAubT | I don’t let go | 2018 | 12 | 7 | 121630 | 0.591 | 0.797 | 0.293 | 1 | 0.0724 | 5 | 0.148 | -7.479 | 1 | 65 | 0.08 | 80.035 | 0.478 |
| 1746Hs7bQJMMYAZvVYM3ig | Two Shots (feat. gnash) | 2018 | 3 | 23 | 179915 | 0.0542 | 0.768 | 0.408 | 1 | 0.0 | 4 | 0.106 | -8.551 | 0 | 61 | 0.0714 | 129.96 | 0.0905 |
| 5SO7pvSFiXwU0e1z7C4X7H | Alcohol You Later | 2018 | 12 | 14 | 186600 | 0.0259 | 0.671 | 0.597 | 0 | 0.0 | 8 | 0.116 | -7.011 | 1 | 62 | 0.0351 | 100.039 | 0.422 |
| 5O9zs6G6RcB6yP1OKwnwiM | Choppa Won't Miss (feat. Young Thug) | 2018 | 5 | 11 | 217960 | 0.0182 | 0.858 | 0.661 | 1 | 0.0 | 0 | 0.115 | -6.544 | 1 | 63 | 0.319 | 163.056 | 0.611 |
| 55GiOwtxNajXVbpjjIzMnD | SWISH | 2018 | 7 | 25 | 194936 | 0.0831 | 0.884 | 0.541 | 1 | 0.0 | 11 | 0.118 | -5.608 | 1 | 70 | 0.221 | 105.009 | 0.274 |
| 0chpIFSjgfd1CPrAxM7PWp | No Rest | 2018 | 8 | 13 | 135535 | 0.0186 | 0.655 | 0.498 | 1 | 4.47e-06 | 9 | 0.139 | -13.147 | 1 | 64 | 0.0479 | 162.861 | 0.508 |
| 5awvelCGpDQHwgZem0ira9 | Lately | 2018 | 11 | 1 | 207000 | 0.213 | 0.718 | 0.632 | 0 | 1.28e-05 | 0 | 0.283 | -4.011 | 1 | 64 | 0.145 | 160.039 | 0.859 |
| 0h31TZMlv0ZLc5yppKngwk | Memories Are Made Of | 2018 | 6 | 1 | 216667 | 0.0159 | 0.694 | 0.718 | 0 | 1.61e-06 | 2 | 0.287 | -6.188 | 1 | 62 | 0.0322 | 121.078 | 0.364 |
| 46Lx5epWOQBA3J86ovnxmV | Workin Out | 2018 | 11 | 26 | 226859 | 0.384 | 0.818 | 0.57 | 1 | 0.000102 | 10 | 0.19 | -8.067 | 0 | 64 | 0.285 | 126.938 | 0.561 |
| 72gv4zhNvRVdQA0eOenCal | Symphony (feat. Zara Larsson) | 2018 | 11 | 30 | 212733 | 0.239 | 0.715 | 0.605 | 0 | 1.41e-05 | 0 | 0.189 | -5.128 | 0 | 74 | 0.0428 | 122.956 | 0.454 |
| 0G2wimhVoDYXbQ6csDxtSf | Sex n' Drugs | 2018 | 2 | 9 | 217778 | 0.299 | 0.831 | 0.32 | 0 | 0.0 | 7 | 0.0757 | -7.916 | 1 | 65 | 0.143 | 80.935 | 0.319 |
| 6OpU3cvY19M2hFxTbLKy5L | Juno | 2018 | 8 | 3 | 231681 | 0.921 | 0.442 | 0.267 | 1 | 0.00026 | 7 | 0.148 | -12.857 | 1 | 63 | 0.0365 | 120.075 | 0.116 |
| 42k9e2hgl98IdgkXLg4pxs | No More (feat. Travis Scott, Kodak Black & 21 Savage) | 2018 | 11 | 6 | 265120 | 0.413 | 0.636 | 0.501 | 1 | 0.0 | 11 | 0.339 | -7.26 | 0 | 65 | 0.329 | 82.309 | 0.196 |
| 1ZLrDPgR7mvuTco3rQK8Pk | Way Back Home (feat. Conor Maynard) - Sam Feldt Edit | 2018 | 12 | 21 | 192453 | 0.269 | 0.737 | 0.747 | 0 | 0.0 | 10 | 0.219 | -4.818 | 1 | 75 | 0.0323 | 105.943 | 0.447 |
| 6PuoZT4kgw5DrUEdnQ6e01 | This Must Be The Place | 2018 | 1 | 12 | 219261 | 0.349 | 0.886 | 0.371 | 0 | 0.00138 | 5 | 0.0985 | -10.542 | 1 | 63 | 0.0465 | 115.026 | 0.928 |
| 1T3E8z6VMXSsACrBSQHTkN | MyBoi - TroyBoi Remix | 2018 | 3 | 9 | 211304 | 0.00626 | 0.879 | 0.58 | 0 | 0.0433 | 2 | 0.0653 | -4.797 | 1 | 68 | 0.0883 | 92.004 | 0.312 |
| 6nVm313QmsPlNllntTart1 | Living Hope | 2018 | 8 | 3 | 327000 | 0.0304 | 0.211 | 0.558 | 0 | 0.0 | 3 | 0.0978 | -6.232 | 1 | 61 | 0.041 | 143.452 | 0.317 |
| 6XyxCBp6x3jvtxXvMN5sAA | 1539 N. Calvert | 2018 | 1 | 19 | 157259 | 0.278 | 0.863 | 0.717 | 1 | 0.0826 | 4 | 0.0991 | -3.143 | 0 | 64 | 0.17 | 152.05 | 0.357 |
| 6VS7wKwtvL2FvTupYSWZ9e | Invincible | 2018 | 12 | 14 | 196387 | 0.141 | 0.724 | 0.6 | 0 | 0.000218 | 10 | 0.104 | -4.391 | 0 | 65 | 0.29 | 86.984 | 0.466 |
| 1hHe7cirScEJWgAS0JFqTL | 7 Am Freestyle | 2018 | 10 | 19 | 192907 | 0.0185 | 0.951 | 0.401 | 1 | 0.0 | 0 | 0.149 | -8.92 | 0 | 63 | 0.411 | 129.976 | 0.539 |
| 1jfBIylYmLMklQOpbWT5s0 | Blame It On The Kids | 2018 | 11 | 16 | 196341 | 0.364 | 0.719 | 0.44 | 0 | 0.0 | 6 | 0.163 | -6.266 | 0 | 67 | 0.166 | 133.013 | 0.505 |
| 5P8lyudWE7HQxb4ludLbEm | Renai Circulation | 2018 | 11 | 2 | 255040 | 0.0524 | 0.814 | 0.785 | 0 | 0.00111 | 1 | 0.111 | -5.679 | 0 | 68 | 0.0396 | 120.009 | 0.941 |
| 3z1QH1O1h15lFROjcECqgi | A Long Way | 2018 | 6 | 1 | 217280 | 0.0194 | 0.483 | 0.713 | 0 | 0.000151 | 2 | 0.316 | -5.027 | 1 | 62 | 0.0295 | 161.905 | 0.594 |
| 2wRkBumdItthjYP9XknImg | Est√° Rico | 2018 | 9 | 28 | 215151 | 0.266 | 0.817 | 0.74 | 0 | 0.0 | 11 | 0.0992 | -4.566 | 0 | 69 | 0.283 | 130.04 | 0.674 |
| 2vHfabj6nFebekTYODqntl | Want You Back | 2018 | 6 | 15 | 173082 | 0.00699 | 0.618 | 0.867 | 1 | 1.03e-06 | 0 | 0.202 | -4.915 | 1 | 68 | 0.0439 | 98.03 | 0.579 |
| 45WkcBtgPS8xulUwEg63X1 | TOGETHER | 2018 | 8 | 15 | 214707 | 0.469 | 0.622 | 0.375 | 1 | 1.28e-05 | 4 | 0.185 | -8.115 | 0 | 63 | 0.208 | 132.887 | 0.357 |
| 6xqbec9MHRmW7qtEINAlRx | Darte Remix | 2018 | 7 | 27 | 512093 | 0.144 | 0.81 | 0.631 | 1 | 1.32e-06 | 4 | 0.451 | -6.541 | 0 | 70 | 0.0938 | 154.976 | 0.573 |
| 7f4bRMqRKRfUNPW2gbTHSt | Save Me From Myself (with NoMBe & Big Gigantic) | 2018 | 10 | 26 | 211686 | 0.0198 | 0.673 | 0.676 | 0 | 1.26e-05 | 4 | 0.428 | -4.888 | 0 | 62 | 0.216 | 82.979 | 0.391 |
| 1LV6zxUFIUyePwsdFHNIYA | El de la Gorra 27 | 2018 | 4 | 20 | 200071 | 0.781 | 0.663 | 0.785 | 0 | 2.33e-06 | 11 | 0.113 | -3.874 | 0 | 67 | 0.0365 | 107.455 | 0.84 |
| 3FskQrDXcY24ur2fCvz35O | Ye | 2018 | 1 | 26 | 231618 | 0.0181 | 0.503 | 0.592 | 1 | 0.00014 | 5 | 0.169 | -5.923 | 0 | 72 | 0.444 | 201.85 | 0.344 |
| 3TgelNfEK1DnYrsHOBxaVJ | El De Los Lentes Gucci | 2018 | 7 | 10 | 125412 | 0.37 | 0.765 | 0.539 | 0 | 0.0 | 11 | 0.039 | -5.028 | 0 | 68 | 0.0437 | 99.94 | 0.965 |
| 1D23GEeQ32aqLw5ZnY3Ivp | COOLEST MONKEY IN THE JUNGLE | 2018 | 5 | 11 | 152213 | 0.324 | 0.893 | 0.676 | 1 | 0.0 | 9 | 0.0968 | -4.867 | 0 | 63 | 0.227 | 125.923 | 0.804 |
| 4BiiOzZCrXEzHRLYcYFiD5 | Hope | 2018 | 12 | 14 | 180120 | 0.488 | 0.773 | 0.699 | 0 | 3.6e-06 | 1 | 0.0814 | -5.982 | 0 | 71 | 0.0958 | 104.941 | 0.513 |
| 6e13443Ve7RGcAUScTgYtl | The Last Of The Real Ones | 2018 | 1 | 19 | 230400 | 0.00599 | 0.576 | 0.872 | 0 | 0.0 | 8 | 0.188 | -5.293 | 1 | 63 | 0.0471 | 115.042 | 0.559 |
| 2yI8omCrTAyrTcK7NRppu7 | Ocean (feat. Jacquees) | 2018 | 6 | 22 | 219437 | 0.268 | 0.709 | 0.264 | 1 | 0.0 | 1 | 0.24 | -17.696 | 0 | 62 | 0.186 | 133.858 | 0.341 |
| 2lRe5wBRm4xaSKTbDn2vLD | Trippy (feat. J. Cole) | 2018 | 11 | 16 | 323587 | 0.0707 | 0.57 | 0.65 | 1 | 0.0 | 2 | 0.339 | -8.538 | 1 | 65 | 0.326 | 96.767 | 0.174 |
| 1j8gLQNfFOI0vs8qcVTWwa | ¿Por Qué Cambiaste De Opinión? | 2018 | 7 | 20 | 241907 | 0.446 | 0.735 | 0.49 | 0 | 3.52e-06 | 5 | 0.129 | -4.937 | 1 | 69 | 0.0299 | 135.159 | 0.89 |
| 4n1bdaKwynQndm47x5HqWX | Blue Tint | 2018 | 6 | 29 | 162800 | 0.013 | 0.888 | 0.372 | 1 | 0.0 | 7 | 0.184 | -10.254 | 0 | 64 | 0.358 | 133.998 | 0.123 |
| 2u6XJ2PPaCyyRJqC0x6TMH | Through the Storm | 2018 | 6 | 8 | 163567 | 0.631 | 0.881 | 0.581 | 1 | 0.0 | 5 | 0.0986 | -5.162 | 1 | 61 | 0.0901 | 78.508 | 0.876 |
| 6euzJRzwBig3NESMppkxOK | All I Got | 2018 | 2 | 16 | 218704 | 0.12 | 0.667 | 0.509 | 0 | 1.91e-06 | 1 | 0.269 | -6.584 | 1 | 64 | 0.0348 | 104.995 | 0.289 |
| 4uTFofZ26M2VgCVq3ixUUY | Haunt U | 2018 | 2 | 24 | 100260 | 0.393 | 0.463 | 0.632 | 1 | 0.0 | 7 | 0.127 | -9.577 | 1 | 68 | 0.0525 | 163.71 | 0.0894 |
| 4ToogKQq58R9TvO40qRuxi | I Wanna Know | 2018 | 7 | 27 | 206727 | 0.501 | 0.432 | 0.85 | 0 | 6.59e-06 | 10 | 0.203 | -4.417 | 1 | 63 | 0.0771 | 82.607 | 0.109 |
| 1KnJwmgLyBfB7u920qLbEq | Romance Garbage | 2018 | 7 | 7 | 122070 | 0.152 | 0.467 | 0.553 | 0 | 0.683 | 6 | 0.291 | -9.692 | 0 | 64 | 0.1 | 144.943 | 0.39 |
| 0FbSpY8RPxTRxi1Fpj9Dz9 | Booty | 2018 | 2 | 27 | 160038 | 0.000831 | 0.938 | 0.549 | 1 | 0.000243 | 1 | 0.082 | -8.368 | 1 | 62 | 0.098 | 144.005 | 0.412 |
| 08zAFXhfyIxq9359NHksdP | Do Not Disturb (feat. Lil Yachty & Offset) | 2018 | 4 | 13 | 157557 | 0.203 | 0.878 | 0.647 | 1 | 0.00185 | 10 | 0.335 | -4.566 | 0 | 64 | 0.0659 | 131.027 | 0.212 |
| 72B1Omo2kmEst4YKO8vOB6 | Scars | 2018 | 3 | 2 | 238307 | 0.048 | 0.417 | 0.524 | 0 | 0.0 | 10 | 0.103 | -6.6 | 1 | 62 | 0.0326 | 75.266 | 0.152 |
| 2SPEg45ZaUFJCg9GwNmRvw | None Of My Business | 2018 | 10 | 19 | 184005 | 0.428 | 0.793 | 0.488 | 0 | 0.0 | 2 | 0.0823 | -5.807 | 0 | 68 | 0.0462 | 133.957 | 0.5 |
| 3ZsLuJ8uU0lE6rGTkDaqEg | Alone Together | 2018 | 6 | 22 | 171440 | 0.00527 | 0.541 | 0.87 | 0 | 0.0 | 1 | 0.0867 | -4.741 | 1 | 62 | 0.122 | 179.975 | 0.665 |
| 2TLDilmv1UmRTfyb7f2Hrx | One That Got Away | 2018 | 6 | 1 | 218733 | 0.00303 | 0.565 | 0.825 | 0 | 0.0 | 10 | 0.0779 | -4.245 | 1 | 61 | 0.0417 | 160.003 | 0.609 |
| 4qjLvvBh5ZeKEPyShKRf06 | Narcissist (feat. The 1975) | 2018 | 8 | 28 | 195502 | 0.218 | 0.543 | 0.682 | 1 | 1.53e-06 | 3 | 0.085 | -5.81 | 1 | 67 | 0.113 | 179.969 | 0.424 |
| 41a7dZcq30Ss5kPMayWRV0 | That’s How You Feel | 2018 | 6 | 29 | 157605 | 0.117 | 0.85 | 0.473 | 1 | 0.0105 | 10 | 0.108 | -5.747 | 0 | 65 | 0.209 | 85.454 | 0.38 |
| 48x8fBWfyeSMzLl5gbsjr9 | COME THRU | 2018 | 10 | 26 | 153169 | 0.163 | 0.66 | 0.596 | 1 | 0.86 | 0 | 0.109 | -8.207 | 1 | 65 | 0.0358 | 77.026 | 0.0757 |
| 1FDV80LC5JnTDAEjPDovER | Worthy Of You | 2018 | 12 | 6 | 190619 | 0.439 | 0.451 | 0.452 | 0 | 0.0 | 1 | 0.478 | -9.363 | 0 | 66 | 0.0574 | 57.523 | 0.386 |
| 33DzKnwuBE6lfOiADwzd5E | TOOTIMETOOTIMETOOTIME | 2018 | 11 | 30 | 207851 | 0.177 | 0.667 | 0.824 | 0 | 0.0152 | 8 | 0.0933 | -5.867 | 1 | 68 | 0.0665 | 114.998 | 0.518 |
| 1x3W8RZxW94lrVGhP95qA6 | You Are The Reason - Duet Version | 2018 | 11 | 30 | 190760 | 0.382 | 0.403 | 0.356 | 0 | 0.0 | 10 | 0.148 | -7.547 | 1 | 72 | 0.0298 | 171.454 | 0.151 |
| 3lWzVNe1yFZlkeBBzUuZYu | Bella Ciao - M√∫sica Original de la Serie la Casa de Papel/ Money Heist | 2018 | 4 | 20 | 139845 | 0.538 | 0.46 | 0.564 | 0 | 0.0 | 9 | 0.191 | -10.329 | 0 | 78 | 0.0343 | 135.178 | 0.962 |
| 18xmRkPWE0sGUzFyNNAAhN | Dying in LA | 2018 | 6 | 22 | 229053 | 0.876 | 0.346 | 0.35 | 0 | 1.98e-06 | 2 | 0.0708 | -4.988 | 1 | 65 | 0.0328 | 126.444 | 0.315 |
| 6DuEfVcF8bmEkiFKr41SiM | Prayed For You - Acoustic | 2018 | 11 | 16 | 205915 | 0.63 | 0.713 | 0.386 | 0 | 0.0 | 4 | 0.151 | -6.995 | 1 | 61 | 0.0291 | 139.968 | 0.52 |
| 5sWbwccBcyHsg5LEKWGZo9 | BRACKETS | 2018 | 4 | 20 | 315771 | 0.19 | 0.675 | 0.567 | 1 | 1.77e-06 | 10 | 0.175 | -9.147 | 0 | 63 | 0.263 | 84.039 | 0.658 |
| 3BLKY8w5XTehYgLb3cFZgg | Deadman's Curve (Live) | 2018 | 6 | 29 | 207453 | 0.731 | 0.392 | 0.496 | 0 | 0.0 | 2 | 0.908 | -9.664 | 1 | 60 | 0.203 | 172.243 | 0.604 |
| 6dJJT9h7FzjenIaa31bw8p | Yo Fui El Elegante | 2018 | 2 | 12 | 146413 | 0.296 | 0.785 | 0.831 | 0 | 0.0 | 9 | 0.176 | -4.439 | 1 | 66 | 0.193 | 134.829 | 0.581 |
| 7IOxauZfx5l18mYfX0GZm6 | Knees | 2018 | 6 | 22 | 206581 | 0.0781 | 0.777 | 0.577 | 0 | 1.02e-06 | 11 | 0.0869 | -6.872 | 0 | 68 | 0.0357 | 109.019 | 0.381 |
| 0r9d60ZYPAlFHq8qnUtkXN | Madura (feat. Bad Bunny) | 2018 | 4 | 13 | 190909 | 0.138 | 0.878 | 0.555 | 0 | 1.34e-06 | 1 | 0.151 | -7.639 | 0 | 70 | 0.202 | 88.029 | 0.751 |
| 645YBsxhFPSOKxGep3uHLv | Love Maze | 2018 | 5 | 18 | 221529 | 0.0487 | 0.636 | 0.876 | 0 | 0.0 | 9 | 0.0728 | -4.652 | 1 | 70 | 0.0617 | 149.932 | 0.759 |
| 6Z7m5DLNPOaPg9T6nXK0rQ | DNA | 2018 | 8 | 24 | 223123 | 0.0223 | 0.599 | 0.774 | 0 | 0.0 | 1 | 0.0669 | -4.014 | 0 | 72 | 0.0541 | 129.819 | 0.696 |
| 48XfojJC87VKLlUotYV0Gm | The Truth Untold | 2018 | 5 | 18 | 242293 | 0.372 | 0.57 | 0.422 | 0 | 0.0 | 1 | 0.127 | -7.561 | 0 | 70 | 0.0422 | 113.007 | 0.169 |
| 46za9pKXWkesH9DCrBbtNU | Low | 2018 | 4 | 28 | 233963 | 0.689 | 0.746 | 0.438 | 1 | 0.103 | 4 | 0.126 | -7.016 | 0 | 64 | 0.0326 | 119.991 | 0.042 |
| 50Hv5NZIM0puIUoBttjpfb | King of the Clouds | 2018 | 6 | 22 | 160747 | 0.237 | 0.285 | 0.922 | 0 | 0.0 | 2 | 0.159 | -1.932 | 0 | 64 | 0.247 | 198.063 | 0.472 |
%sql SELECT * FROM dim_users LIMIT 100;
postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database * postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 100 rows affected.
| id | user_id | full_name | gender | level |
|---|---|---|---|---|
| 0 | 1 | Adelyn Jordan | F | free |
| 1 | 2 | Agnes Lazo | F | paid |
| 2 | 3 | Alyssa Fuentebella | F | free |
| 3 | 4 | Amos Changchoco | M | paid |
| 4 | 5 | Angelo Santiago | M | paid |
| 5 | 6 | Anna Ramos | F | paid |
| 6 | 7 | Argel Alvezo | M | paid |
| 7 | 8 | Bianca Cirio | F | paid |
| 8 | 9 | Billy Alegrado | M | paid |
| 9 | 10 | Bonnie Ruiz | F | paid |
| 10 | 11 | Chris Soriano | M | paid |
| 11 | 12 | Cid Ual | M | free |
| 12 | 13 | Dale Garcia | M | paid |
| 13 | 14 | Dan Nuesca | M | free |
| 14 | 15 | Donna Magugat | F | paid |
| 15 | 16 | Elizabeth Brooks | F | free |
| 16 | 17 | Emily Benson | F | paid |
| 17 | 18 | Fred Rara | M | paid |
| 18 | 19 | Gab Cadena | M | paid |
| 19 | 20 | Gelo Timajo | M | paid |
| 20 | 21 | Gil Uy | F | paid |
| 21 | 22 | Hayden Brock | F | paid |
| 22 | 23 | HJ Bayaborda | M | free |
| 23 | 24 | Jaffy Tan | M | paid |
| 24 | 25 | Jazel Jayme | F | free |
| 25 | 26 | Jill Aiken | F | free |
| 26 | 27 | Joaqs Gonzales | M | free |
| 27 | 28 | John Gacal | M | free |
| 28 | 29 | Julia Las | F | free |
| 29 | 30 | K-Ann Carandang | F | paid |
| 30 | 31 | Karen Reyes | F | paid |
| 31 | 32 | Ken Inocencio | M | free |
| 32 | 33 | Kimberly Hohl | F | paid |
| 33 | 34 | Lauren Frank | F | free |
| 34 | 35 | Law Guidote | M | paid |
| 35 | 36 | Lily Cooper | F | paid |
| 36 | 37 | Magdalene Herman | F | paid |
| 37 | 38 | Mandy Mapa | M | paid |
| 38 | 39 | Manu Gaspar | M | free |
| 39 | 40 | Marc Castro | M | free |
| 40 | 41 | Margs Baluyut | F | free |
| 41 | 42 | Mark Acot | M | paid |
| 42 | 43 | Marvee dela Resma | M | paid |
| 43 | 44 | Mason Hao | M | paid |
| 44 | 45 | Mavel Paderes | F | free |
| 45 | 46 | Miguel Pingol | M | paid |
| 46 | 47 | Mike Tanjango | M | free |
| 47 | 48 | Mimay Rodrigo | F | paid |
| 48 | 49 | Nancy Cruz | F | paid |
| 49 | 50 | Norman Lapid | M | paid |
| 50 | 51 | Pao de Guzman | M | paid |
| 51 | 52 | Paolo Punzalan | M | paid |
| 52 | 53 | Pat Donato | F | paid |
| 53 | 54 | Patrick La Rosa | M | paid |
| 54 | 55 | Patsy Hanlon | F | paid |
| 55 | 56 | Pauline Guevarra | F | free |
| 56 | 57 | Sharyl Sy | F | paid |
| 57 | 58 | Stacey Crosby | F | paid |
| 58 | 59 | Susan Boyer | F | paid |
| 59 | 60 | Toby Fernando | M | paid |
| 60 | 61 | Ven Velante | M | free |
| 61 | 62 | Vince Rivera | M | paid |
| 62 | 63 | Viola Conner | F | paid |
| 63 | 64 | Weddy Diamada | M | paid |
| 64 | 65 | Xidy del Rosario | F | paid |
| 65 | 66 | Zhoya Ginez | F | free |
| 0 | 1 | Adelyn Jordan | F | free |
| 1 | 2 | Agnes Lazo | F | paid |
| 2 | 3 | Alyssa Fuentebella | F | free |
| 3 | 4 | Amos Changchoco | M | paid |
| 4 | 5 | Angelo Santiago | M | paid |
| 5 | 6 | Anna Ramos | F | paid |
| 6 | 7 | Argel Alvezo | M | paid |
| 7 | 8 | Bianca Cirio | F | paid |
| 8 | 9 | Billy Alegrado | M | paid |
| 9 | 10 | Bonnie Ruiz | F | paid |
| 10 | 11 | Chris Soriano | M | paid |
| 11 | 12 | Cid Ual | M | free |
| 12 | 13 | Dale Garcia | M | paid |
| 13 | 14 | Dan Nuesca | M | free |
| 14 | 15 | Donna Magugat | F | paid |
| 15 | 16 | Elizabeth Brooks | F | free |
| 16 | 17 | Emily Benson | F | paid |
| 17 | 18 | Fred Rara | M | paid |
| 18 | 19 | Gab Cadena | M | paid |
| 19 | 20 | Gelo Timajo | M | paid |
| 20 | 21 | Gil Uy | F | paid |
| 21 | 22 | Hayden Brock | F | paid |
| 22 | 23 | HJ Bayaborda | M | free |
| 23 | 24 | Jaffy Tan | M | paid |
| 24 | 25 | Jazel Jayme | F | free |
| 25 | 26 | Jill Aiken | F | free |
| 26 | 27 | Joaqs Gonzales | M | free |
| 27 | 28 | John Gacal | M | free |
| 28 | 29 | Julia Las | F | free |
| 29 | 30 | K-Ann Carandang | F | paid |
| 30 | 31 | Karen Reyes | F | paid |
| 31 | 32 | Ken Inocencio | M | free |
| 32 | 33 | Kimberly Hohl | F | paid |
| 33 | 34 | Lauren Frank | F | free |
%sql SELECT * FROM fact_listens LIMIT 100;
postgresql://love_keeper:***@finalproj-db.cydd5y33n4ue.us-east-1.rds.amazonaws.com/love_database * postgresql://songmanager:***@new-cluster.cjptwjq0k7m8.us-east-1.redshift.amazonaws.com:5439/songsdb 100 rows affected.
| user_id | song_id | title | date_id |
|---|---|---|---|
| 26 | 2UVbBKQOdFAekPTRsnkzcf | Stir Fry | 1 |
| 50 | 2UVbBKQOdFAekPTRsnkzcf | Stir Fry | 2 |
| 24 | 2UVbBKQOdFAekPTRsnkzcf | Stir Fry | 3 |
| 15 | 2UVbBKQOdFAekPTRsnkzcf | Stir Fry | 4 |
| 52 | 2UVbBKQOdFAekPTRsnkzcf | Stir Fry | 5 |
| 11 | 2UVbBKQOdFAekPTRsnkzcf | Stir Fry | 6 |
| 23 | 1Jl21sidRpEdlVBvYbssLx | Sin (feat. Jaden Smith) | 7 |
| 13 | 1Jl21sidRpEdlVBvYbssLx | Sin (feat. Jaden Smith) | 8 |
| 40 | 1Jl21sidRpEdlVBvYbssLx | Sin (feat. Jaden Smith) | 9 |
| 10 | 1Jl21sidRpEdlVBvYbssLx | Sin (feat. Jaden Smith) | 10 |
| 13 | 1Jl21sidRpEdlVBvYbssLx | Sin (feat. Jaden Smith) | 11 |
| 36 | 1Jl21sidRpEdlVBvYbssLx | Sin (feat. Jaden Smith) | 12 |
| 27 | 1Jl21sidRpEdlVBvYbssLx | Sin (feat. Jaden Smith) | 13 |
| 43 | 1Jl21sidRpEdlVBvYbssLx | Sin (feat. Jaden Smith) | 14 |
| 23 | 20dkebBtPR4AJ8vVos41Gw | Rock Salt and Nails (Live) | 15 |
| 27 | 20dkebBtPR4AJ8vVos41Gw | Rock Salt and Nails (Live) | 16 |
| 32 | 20dkebBtPR4AJ8vVos41Gw | Rock Salt and Nails (Live) | 17 |
| 58 | 20dkebBtPR4AJ8vVos41Gw | Rock Salt and Nails (Live) | 18 |
| 1 | 20dkebBtPR4AJ8vVos41Gw | Rock Salt and Nails (Live) | 19 |
| 48 | 20dkebBtPR4AJ8vVos41Gw | Rock Salt and Nails (Live) | 20 |
| 23 | 6t94PGXYAuo9GbJEnMpPcW | La Forma en Que Me Miras | 21 |
| 58 | 6t94PGXYAuo9GbJEnMpPcW | La Forma en Que Me Miras | 22 |
| 15 | 6t94PGXYAuo9GbJEnMpPcW | La Forma en Que Me Miras | 23 |
| 12 | 6t94PGXYAuo9GbJEnMpPcW | La Forma en Que Me Miras | 24 |
| 5 | 6t94PGXYAuo9GbJEnMpPcW | La Forma en Que Me Miras | 25 |
| 47 | 4TwVtW8hS5LyLoDtJGpUOg | History | 26 |
| 57 | 4TwVtW8hS5LyLoDtJGpUOg | History | 27 |
| 32 | 4TwVtW8hS5LyLoDtJGpUOg | History | 28 |
| 14 | 4TwVtW8hS5LyLoDtJGpUOg | History | 29 |
| 54 | 4TwVtW8hS5LyLoDtJGpUOg | History | 30 |
| 25 | 4TwVtW8hS5LyLoDtJGpUOg | History | 31 |
| 38 | 4TwVtW8hS5LyLoDtJGpUOg | History | 32 |
| 15 | 4TwVtW8hS5LyLoDtJGpUOg | History | 33 |
| 41 | 4TwVtW8hS5LyLoDtJGpUOg | History | 34 |
| 23 | 4TwVtW8hS5LyLoDtJGpUOg | History | 35 |
| 22 | 4TwVtW8hS5LyLoDtJGpUOg | History | 36 |
| 40 | 4TwVtW8hS5LyLoDtJGpUOg | History | 37 |
| 23 | 6fS1CEMY4LlvQNWuUMoWEQ | Mockingbird | 38 |
| 31 | 6fS1CEMY4LlvQNWuUMoWEQ | Mockingbird | 39 |
| 48 | 6fS1CEMY4LlvQNWuUMoWEQ | Mockingbird | 40 |
| 9 | 6fS1CEMY4LlvQNWuUMoWEQ | Mockingbird | 41 |
| 25 | 6fS1CEMY4LlvQNWuUMoWEQ | Mockingbird | 42 |
| 24 | 6fS1CEMY4LlvQNWuUMoWEQ | Mockingbird | 43 |
| 26 | 4ve2uzqdwnHr20G5YgMMqr | Missin You Crazy | 44 |
| 2 | 4ve2uzqdwnHr20G5YgMMqr | Missin You Crazy | 45 |
| 42 | 4ve2uzqdwnHr20G5YgMMqr | Missin You Crazy | 46 |
| 29 | 4ve2uzqdwnHr20G5YgMMqr | Missin You Crazy | 47 |
| 34 | 4ve2uzqdwnHr20G5YgMMqr | Missin You Crazy | 48 |
| 66 | 4ve2uzqdwnHr20G5YgMMqr | Missin You Crazy | 49 |
| 18 | 4ve2uzqdwnHr20G5YgMMqr | Missin You Crazy | 50 |
| 37 | 4ve2uzqdwnHr20G5YgMMqr | Missin You Crazy | 51 |
| 15 | 4ve2uzqdwnHr20G5YgMMqr | Missin You Crazy | 52 |
| 11 | 4ve2uzqdwnHr20G5YgMMqr | Missin You Crazy | 53 |
| 16 | 4ve2uzqdwnHr20G5YgMMqr | Missin You Crazy | 54 |
| 35 | 4ve2uzqdwnHr20G5YgMMqr | Missin You Crazy | 55 |
| 52 | 4ve2uzqdwnHr20G5YgMMqr | Missin You Crazy | 56 |
| 47 | 0WVAQaxrT0wsGEG4BCVSn2 | Born To Be Yours | 57 |
| 57 | 0WVAQaxrT0wsGEG4BCVSn2 | Born To Be Yours | 58 |
| 35 | 0WVAQaxrT0wsGEG4BCVSn2 | Born To Be Yours | 59 |
| 1 | 0WVAQaxrT0wsGEG4BCVSn2 | Born To Be Yours | 60 |
| 36 | 0WVAQaxrT0wsGEG4BCVSn2 | Born To Be Yours | 61 |
| 9 | 0WVAQaxrT0wsGEG4BCVSn2 | Born To Be Yours | 62 |
| 29 | 0WVAQaxrT0wsGEG4BCVSn2 | Born To Be Yours | 63 |
| 52 | 0WVAQaxrT0wsGEG4BCVSn2 | Born To Be Yours | 64 |
| 44 | 0WVAQaxrT0wsGEG4BCVSn2 | Born To Be Yours | 65 |
| 15 | 0WVAQaxrT0wsGEG4BCVSn2 | Born To Be Yours | 66 |
| 39 | 0WVAQaxrT0wsGEG4BCVSn2 | Born To Be Yours | 67 |
| 47 | 5qHirGR7M9tdm6C17DlzSY | REEL IT IN | 68 |
| 33 | 5qHirGR7M9tdm6C17DlzSY | REEL IT IN | 69 |
| 2 | 5qHirGR7M9tdm6C17DlzSY | REEL IT IN | 70 |
| 39 | 5qHirGR7M9tdm6C17DlzSY | REEL IT IN | 71 |
| 42 | 5qHirGR7M9tdm6C17DlzSY | REEL IT IN | 72 |
| 24 | 5qHirGR7M9tdm6C17DlzSY | REEL IT IN | 73 |
| 27 | 5qHirGR7M9tdm6C17DlzSY | REEL IT IN | 74 |
| 36 | 5qHirGR7M9tdm6C17DlzSY | REEL IT IN | 75 |
| 30 | 5qHirGR7M9tdm6C17DlzSY | REEL IT IN | 76 |
| 38 | 5qHirGR7M9tdm6C17DlzSY | REEL IT IN | 77 |
| 47 | 5N5k9nd479b1xpDZ4usjrg | Promises (with Sam Smith) | 78 |
| 45 | 5N5k9nd479b1xpDZ4usjrg | Promises (with Sam Smith) | 79 |
| 27 | 5N5k9nd479b1xpDZ4usjrg | Promises (with Sam Smith) | 80 |
| 11 | 5N5k9nd479b1xpDZ4usjrg | Promises (with Sam Smith) | 81 |
| 21 | 5N5k9nd479b1xpDZ4usjrg | Promises (with Sam Smith) | 82 |
| 51 | 5N5k9nd479b1xpDZ4usjrg | Promises (with Sam Smith) | 83 |
| 42 | 5N5k9nd479b1xpDZ4usjrg | Promises (with Sam Smith) | 84 |
| 47 | 6hkQ6OQ6nhe7QCckH91aGa | Victory Lap (feat. Stacy Barthe) | 85 |
| 60 | 6hkQ6OQ6nhe7QCckH91aGa | Victory Lap (feat. Stacy Barthe) | 86 |
| 54 | 6hkQ6OQ6nhe7QCckH91aGa | Victory Lap (feat. Stacy Barthe) | 87 |
| 62 | 6hkQ6OQ6nhe7QCckH91aGa | Victory Lap (feat. Stacy Barthe) | 88 |
| 59 | 6hkQ6OQ6nhe7QCckH91aGa | Victory Lap (feat. Stacy Barthe) | 89 |
| 64 | 6hkQ6OQ6nhe7QCckH91aGa | Victory Lap (feat. Stacy Barthe) | 90 |
| 9 | 6hkQ6OQ6nhe7QCckH91aGa | Victory Lap (feat. Stacy Barthe) | 91 |
| 36 | 6hkQ6OQ6nhe7QCckH91aGa | Victory Lap (feat. Stacy Barthe) | 92 |
| 19 | 6hkQ6OQ6nhe7QCckH91aGa | Victory Lap (feat. Stacy Barthe) | 93 |
| 19 | 6hkQ6OQ6nhe7QCckH91aGa | Victory Lap (feat. Stacy Barthe) | 94 |
| 56 | 6hkQ6OQ6nhe7QCckH91aGa | Victory Lap (feat. Stacy Barthe) | 95 |
| 35 | 0Y9D1Bc4fAkeZnVsGKdij6 | WANTED U | 96 |
| 33 | 0Y9D1Bc4fAkeZnVsGKdij6 | WANTED U | 97 |
| 6 | 0Y9D1Bc4fAkeZnVsGKdij6 | WANTED U | 98 |
| 40 | 0Y9D1Bc4fAkeZnVsGKdij6 | WANTED U | 99 |
| 16 | 0Y9D1Bc4fAkeZnVsGKdij6 | WANTED U | 100 |
import pandas as pd
import tkinter
import customtkinter
import os
from tkinter import *
from PIL import ImageTk, Image
loc_df = pd.read_csv('https://9kuki1mnrb.execute-api.us-east-1.amazonaws.com/deploy/recommender/loc_match.csv')
song_df = pd.read_csv('https://9kuki1mnrb.execute-api.us-east-1.amazonaws.com/deploy/recommender/song_match.csv')
user_df = pd.read_csv('https://9kuki1mnrb.execute-api.us-east-1.amazonaws.com/deploy/recommender/user_map.csv')
user_df['userid'] = user_df['userid'].astype(str)
user_map = pd.Series(user_df.userfullname.values, index=user_df.userid).to_dict()
loc_df.columns = loc_df.columns.astype(str)
loc_df.index = loc_df.index.astype(str)
loc_df['userID'] = loc_df['userID'].astype(str)
loc_df = loc_df.rename(columns=user_map)
loc_df['userID'] = loc_df['userID'].apply(lambda x: user_map[x])
new_cols = song_df['userID'].tolist()
new_cols.insert(0, 'userID')
song_df.columns = new_cols
song_df.columns = song_df.columns.astype(str)
song_df.index = song_df.index.astype(str)
song_df['userID'] = song_df['userID'].astype(str)
song_df = song_df.rename(columns=user_map)
song_df['userID'] = song_df['userID'].apply(lambda x: user_map[x])
def matches():
user_id = entry.get()
max_dist = slider.get()
loc_match = loc_df[loc_df['userID'] == user_id].drop(['userID', user_id], axis=1).T
loc_match.columns = ['matches']
loc_match = list(loc_match[loc_match['matches'] < max_dist].index)
song_score = song_df[song_df['userID'].isin(loc_match)][user_id]
match_dict = {k:v for k, v in zip(loc_match, song_score)}
sorted_matches = sorted(match_dict, key=match_dict.get, reverse=True)
output = (str('You Found:')+'\n'+'\n'+
str(sorted_matches[0])+'\n'+'\n'+
(sorted_matches[1])+'\n'+'\n'+
str(sorted_matches[2]))
label.config(text=output, width=100, height=150)
customtkinter.set_appearance_mode("dark") # Modes: "System" (standard), "Dark", "Light"
customtkinter.set_default_color_theme("blue") # Themes: "blue" (standard), "green", "dark-blue"
root_tk = customtkinter.CTk()
root_tk.geometry(f"{300}x{500}")
root_tk.title("Tindify")
def get_dist(value):
label2.config(text=f"Looking {int(value)}km away")
img = ImageTk.PhotoImage(Image.open("logo.png"))
panel = Label(root_tk, image = img)
panel.pack(side = "top", fill = "both", expand = "yes")
entry = customtkinter.CTkEntry(master=root_tk,
placeholder_text="What's your name?",
width=150,
height=25)
entry.place(relx=0.5, rely=0.73, anchor=tkinter.CENTER)
label2=customtkinter.CTkLabel(root_tk, text='',bg_color='#424242')
label2.place(relx=0.5, rely=0.80,anchor=tkinter.CENTER)
slider = customtkinter.CTkSlider(master=root_tk,
from_=0, to=50,
command=get_dist,
width= 150, button_color='#E84B76')
slider.place(relx=0.5, rely=0.87, anchor=tkinter.CENTER)
button = customtkinter.CTkButton(root_tk, text="Find Love", command=matches, fg_color='#E84B76')
button.place(relx=0.5, rely=0.95, anchor=tkinter.CENTER)
label=customtkinter.CTkLabel(root_tk, text='',bg_color='#424242', width=100, height=100)
label.place(relx=0.5, rely=0.55,anchor=tkinter.CENTER)
root_tk.mainloop()